Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |