Forum Discussion

JMSHI007's avatar
JMSHI007
Regular Visitor
4 years ago

Cumulative Sales Report

Hello All, 

 

I'm new user and new to the PBi community.

I have a problem in getting Previous Year (2021) cumlated sales showing full againest that of YTD 2022 CumSales for my report. 

Anyone can help with right DAX formular? Thanks! 

 

Here is the report: 

 

Here is the Formular: 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    JMSHI007 , Try a measure like

     

    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

     

    Cumm year behind

    Cumm Sales =

    var _max1 = max(date[Date])

    var _max = date(year(_max1)-1, month(_max1), day(_max1))

    return

    CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=_max))

    • JMSHI007's avatar
      JMSHI007
      Regular Visitor
      4 years ago

      amitchandak 

      Thanks you for your fast reply. 

      I've applied your solution, it works with pending issue still. 

      It gives me all 4 years YTDcumulated instead of Y2021.

      We already fix year-reset per year before. 

      Thank you so much.