locked
MDX - To Calculate & Display Last One Year Based On Selected Date RRS feed

  • Question

  • Hi All,

    Here by I am seeking some pointer to resolve one problem which I am facing right now.

    I have 2 date dimensions in my solution:

    ValueDate & TradeDate

    ValueDate -> Stores Date & Time when a transaction has happened (system date)

    TradeDate -> Store Date & Time of transaction (for which date that transaction is)

    example:

    ValueDate   TradeDate

    2017-05-30  2017-05-29

    (above we did a transaction on 30th for the date of 29th - basically back date entry)

    I have a measure group which basically does SUM based of values based on the selected TradeDate. There is a catch, for some business reason we need to display data for last whole year (there are not much transactions).

    So what we need is if one selected:

    TradeDate = 29-05-2017

    It should give us data till last one year i.e. 30-05-2016

    I am trying to write a scope statement for the same (like below) but it does not seems restricting the result set.

    SCOPE (FILTER([Trade Date].[Trade Calendar Year].[Trade Date],[Trade Date].[Trade Date].MEMBERVALUE <= [Trade Date].[Trade Date].MEMBERVALUE AND [Trade Date].[Trade Date].MEMBERVALUE - [Trade Date].[Trade Date].MEMBERVALUE <= 364));
        THIS = ([Measures].[Notional Amount]);
    END SCOPE;

    Any suggestions/pointers will be greatly appreciated.

    Regards

    Gurpreet Sethi


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, May 30, 2017 4:12 AM

All replies

  • Hi Gurpreet,

    Thanks for your question.

    In this scenario, you should create a cumulative measure called [Measures].[Cumulative Measure] as below:

    IIF([Trade Date].[Trade Calendar Year].CurrentMember.level IS 
        [Trade Date].[Trade Calendar Year].[Trade Date], 
    SUM(
    (ParallelPeriod ([Trade Date].[Trade Calendar Year].[Calendar Year]  
       , 1  
       , [Trade Date].[Trade Calendar Year].CurrentMember)).LAG(-1): 
    [Trade Date].[Trade Calendar Year].CurrentMember,
    [Measures].[Notional Amount]),
    NULL)


    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, May 30, 2017 8:48 AM
  • Thanks Wilson,

    But we also need to display all trade dates & value dates for entire year along with sum. With above its just display data for selected trade date.

    Regards

    Gurpreet Sethi


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, May 30, 2017 9:01 AM
  • Hi Gurpreet,

    Thanks for your response.

    In this scenario, you can implement drillthrough for this calculated measure [Measures].[Cumulative Measure] .

    For more detailed information, please refer to blog Drillthrough on calculated measures .


    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

    Wednesday, May 31, 2017 5:20 AM