locked
How to get the prior year MTD value ? RRS feed

  • Question

  • I have to create a view with YoY values for each month in the current fiscal year. For the months that are over, I can use the values from the entire month for both current and prior years to calculate the YoY. However, for the running month, I need to get the MTD values for both current and prior years. I tried using this formula using SAMEPERIODLASTYEAR but it is returning the entire month value.

    PrevMonthRevF = CALCULATE(SUM(Fact[Revenue]),SAMEPERIODLASTYEAR(DATESMTD(Dim_Time[Date]))) 


    How do I get only the MtD value for the same month from last year ?

    Tuesday, May 15, 2018 5:20 PM

Answers

  • Hi ChristianBoeur,

    Thanks for your question.

    In this scenario, please try below DAX formula:

    PrevMonthRevF =
    CALCULATE (
        TOTALMTD ( SUM ( Fact[Revenue] ), Dim_Time[Date] ),
        SAMEPERIODLASTYEAR ( DATESMTD ( Dim_Time[Date] ) )
    )

    See below DAX formula tested with my sample data:

    MTD LY =
    CALCULATE (
        TOTALMTD ( SUM ( 'Calendar'[Amount] ), 'Calendar'[Date] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
    


    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

    • Marked as answer by krmarshal Wednesday, May 16, 2018 9:38 PM
    Wednesday, May 16, 2018 3:19 AM