Forum Discussion

NickProp28's avatar
NickProp28
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

YTD, MTD and variance

Dear Community, 

 

Good day!

Here is my raw data with YTD andLYTD measure.

TotalYTD = TOTALYTD(SUM(CP[Value]),'Calendar'[Date])
TotalLYTD = CALCULATE([TotalYTD],SAMEPERIODLASTYEAR('Calendar'[Date]))
ChangeYoY = DIVIDE([TotalYTD] - [TotalLYTD],[TotalLYTD],0)

The figure is work fine when there have no month filter. But when its come to month filter, 

The number is run, suppose current year is 13659.16 while last year is 19061.32. 40.50K looks like accumulated. 

What should I do in order to get the excatly year to year and month to month number correctly when month and year filter applied? 

Any help will appreciated. 

Thanks

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    NickProp28 , The number is correct for YTD. Maybe you need MTD vs Last year MTD

     

    TotalLYTD = TOTALMTD(SUM(CP[Value]),dateadd('Calendar'[Date],-1,year))

    TotalYTD = TOTALYTD(SUM(CP[Value]),'Calendar'[Date])

     

    Please find the file, The YTD values are correct. If you need only MTD, use the MTD formula

     

    MTD is not added to the file

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    NickProp28 , Try last year like

    TotalLYTD = TOTALYTD(SUM(CP[Value]),dateadd('Calendar'[Date],-1,year))

     

    As I doubt sample period last year will continue in past

    • NickProp28's avatar
      NickProp28
      Icon for Post Partisan rankPost Partisan
      5 years ago

      Dear amitchandak ,

       

      Thanks for the reply. 
      Have revised the code , but result is still the same.

      Here is the table in all month, result look like accumulated.

      Kindly advise

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        NickProp28 , 40K for YTD for Feb 2020, Is what it should give.

        Make sure you are using the column from the date table in the slicer and table.

         

        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.