Forum Discussion
undefined
I need to calculate a cumulative total.
How do I do this
2 Replies
- some_bihCommunity 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!
- ChiragGarg2512Solution Sage
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.