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,
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
Solved! Go to 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))
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))
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))
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |