Forum Discussion

akhassim's avatar
akhassim
New Member
2 years ago

undefined

I need to calculate a cumulative total.

How do I do this

 

2 Replies

  • some_bih's avatar
    some_bih
    Community Champion

    Hi akhassim please create measure as below Cumulative test.

    If you do not have already Calendar / Date table, create one using link as instruction below

     

    Cumulative test =
    VAR __max_date = MAX ( 'Date'[Date] ) -- your Date table
    RETURN
    CALCULATE (
    SUM(Cost Actual)],
    'Date'[Date] <= __max_date,
    ALL ( Date )
    )

     

    Create Calendar Table Microsoft Learn

    https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

     

    Did I answer your question? Kudos appreciated / accept solution!

  • akhassim , If the requirement is to continue addition even when the year ends, so the dax will work some thing like this

     

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

     

    Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

     

    If the sum has to reset every year, the dax will work something like this:

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

     

    Thank You.