Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @pvural,
If I understand you correctly, the formula below should work in your scenario.
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
Hi @pvural,
If I understand you correctly, the formula below should work in your scenario.
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
Thank you very much ... This formula generated the "expected" chart.
Appeciate your time and response on this 🙂
Petek
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |