Running Total Reset based on Column Attribute

[Anonymous]

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

Thank you very much ... This formula generated the "expected" chart.

Appeciate your time and response on this 🙂

Petek