Forum Discussion

Sadr_Hoda's avatar
Sadr_Hoda
Frequent Visitor
3 years ago

Last year sum

Hi,

 

I am unable to calculate last year cumulative sum for Dynamic table like with MTD and YTD.

 

below formula I use for current year and working perfectly.

 

Current Period MTD/YTD =
SWITCH([SelectedPeriod],
1, [Total current year consol.],
2, CALCULATE([Total current year consol.], DATESYTD('Calendar'[Date])))
 
Thanks in advance
Need Help

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Sadr_Hoda 

     

    Please try this measure

    LY Period MTD/YTD =
SWITCH (
    [SelectedPeriod],
    1, CALCULATE ( [Total Sales], DATEADD ( 'Calendar'[Date], -1, YEAR ) ),
    2, CALCULATE ( [Total Sales], DATESYTD ( DATEADD ( 'Calendar'[Date], -1, YEAR ) ) )
)

     

    In above example, Total Sales is a simple measure with SUM: Total Sales = SUM('Table'[Sales])

     
     
    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.
    • Sadr_Hoda's avatar
      Sadr_Hoda
      Frequent Visitor
      3 years ago

      Thanks for your advise, however, it is not working.

       

      I am unable to compute cumulative prior period balance. 

       

      I have a data from 2016 onwards and want to compare month to month and period to period wise.