Cumulative sum reset every start of year

[gavinfb]

Hello,

I am trying to run a cumulative sum that start the sum every begining of the year.  I'm not sure how to do the reset every year despite going through pages of the forum.  I've used the following formula to create the "Cumu Return SKU Price $":

Cumu Return SKU Price $ = 
CALCULATE(
SUMX('30 BME Customer Return','30 BME Customer Return'[Return Unit Price $]),
filter(
all('30 BME Customer Return'),
'30 BME Customer Return'[Creation Date]<=max('30 BME Customer Return'[Creation Date])))

But this is what I'm getting: the cummulative is not resetting in January of the next year.

 

Any guidance would be greatly appreciated.  

thanks

[Greg_Deckler · Accepted solution]

Probably something along the lines of:

Cumu Return SKU Price $ = 
VAR __year = MAX([Year])
RETURN
CALCULATE(
SUMX('30 BME Customer Return','30 BME Customer Return'[Return Unit Price $]),
filter(
all('30 BME Customer Return'),
'30 BME Customer Return'[Creation Date]<=max('30 BME Customer Return'[Creation Date]) && YEAR([Creation Date]) = __year))

[gavinfb]

thanks Greg for the suggestion.  Glad that i now know how to reset by a specific period!  I've teaked your suggestion a wee bit to get the calculation right.  

Cumu Return SKU Price $ = 
VAR __year = MAX('30 BME Customer Return'[Creation Year])
RETURN
CALCULATE(
SUM ('30 BME Customer Return'[Return SKU Price $]),
filter(
all('30 BME Customer Return'),
'30 BME Customer Return'[Creation Date]<=max('30 BME Customer Return'[Creation Date]) && YEAR([Creation Date]) = __year))