locked
Daily Return Histogram from Cube RRS feed

  • Question

  • Hi,

    I'm facing a bit of a challenge with developing an histogram of daily returns,

    I have a cube with fact table that includes the keys:

    TimeKey

    FundKey

    StrategyKey

    AssetClassKey

    AssetKey

    and Value field that holds the values i want to count.

    plus dimensions for each one of the keys to the related table.

    I need to add a calculated measure that will calculate the return difference from current row to previous row

    something like Current [Value]/Previous [Value] - 1.

    the x-axis for the histogram should be dynamic ranges of the Daily Return based on the filter that the user will choose.

    the y-axis is the distinct count.

    I want to implement the UI on Power BI dashboard.

    Something like this:

    Any suggestions would be very valuable!

    Best Regards,
    Eran

    Monday, June 19, 2017 7:57 AM

All replies

  • Hi Eran,

    Thanks for your question.

    If i understand you correctly, you want to create a calculated measure that will calculate the return difference from current row to previous row,something like Current [Value]/Previous [Value] - 1, right?

    In this scenario, you can create a calculated measure called [Measures].[Diff] as below:

    [Measures].[Diff] AS
    ([Time].[Day].CurrentMember, [Measures].[Your Measure])/
    ([Time].[Day].CurrentMember.PrevMember, [Measures].[Your Measure])
    -1
    you may also need to  create a dummy dimension to achieve your requirement.

    Following is an example you may want to reference:
    SSAS: Using fake dimension and scopes for dynamic ranges



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, June 27, 2017 6:35 AM