Forum Discussion
Running Total Reset based on Column Attribute
Hello everyone
I have read numearous articles/posts regarding running total or cumulative total, but couldn't find anything as to how to reset the running total at the beginning of each year.
I used Quick Measure function on Power BI to calculate the running total as follows:
Cumulative_Net Revenue =
CALCULATE(
SUM('Project Details_USD'[MTD Net Revenue]),
FILTER(
ALLSELECTED('DateKey'[Date]),
ISONORAFTER('DateKey'[Date], MAX('DateKey'[Date]), DESC)
)
)
But cannot figure out where to add the filter so that the January running total of each year equals to to January value of that year as opposed to it just continuosly adds up previous month cumulative to current month.
Below on the left is the screenshot of expected graph, and on the right what I got with quick measure formula:
Any help is greatly appreciated
Thank you,
Petek
Hi Anonymous,
If I understand you correctly, the formula below should work in your scenario. :smileyhappy:
Cumulative_Net Revenue = VAR currentYear = YEAR ( MAX ( 'DateKey'[Date] ) ) VAR currentMonth = MONTH ( MAX ( 'DateKey'[Date] ) ) RETURN CALCULATE ( SUM ( 'Project Details_USD'[MTD Net Revenue] ), FILTER ( ALL ( 'DateKey' ), YEAR ( 'DateKey'[Date] ) = currentYear && MONTH ( 'DateKey'[Date] ) <= currentMonth ) )Regards
2 Replies
Hi Anonymous,
If I understand you correctly, the formula below should work in your scenario. :smileyhappy:
Cumulative_Net Revenue = VAR currentYear = YEAR ( MAX ( 'DateKey'[Date] ) ) VAR currentMonth = MONTH ( MAX ( 'DateKey'[Date] ) ) RETURN CALCULATE ( SUM ( 'Project Details_USD'[MTD Net Revenue] ), FILTER ( ALL ( 'DateKey' ), YEAR ( 'DateKey'[Date] ) = currentYear && MONTH ( 'DateKey'[Date] ) <= currentMonth ) )Regards