Forum Discussion
Cumulative sum that resets every year
Hi,
I guess my question is simple, I want a cummlative sum that resets every year. I went through almost all the threads here and tried the formulas with no luck. Here is a sample of my data. The formula I used is:
Cumm.approved=
VAR __year = MAX(projectTrendingData[Date].[Year])
RETURN
CALCULATE(
SUM (projectTrendingData[Approved]),
filter(
all(projectTrendingData),
projectTrendingData[Date]<=max(projectTrendingData[Date]) && YEAR([Date]) = __year))
I simply want to produce the cummulative sum for the Approved column and get it to reset every year.
| Date | Approved | Cumm.approved |
| Thursday, January 3, 2019 | 4 | |
| Thursday, January 17, 2019 | 1 | |
| Thursday, January 31, 2019 | 7 | |
| Thursday, February 14, 2019 | 6 | |
| Thursday, February 28, 2019 | 9 | |
| Thursday, March 14, 2019 | 13 | |
| Thursday, March 28, 2019 | 19 | |
| Thursday, April 11, 2019 | 9 | |
| Thursday, April 25, 2019 | 10 | |
| Thursday, May 9, 2019 | 16 | |
| Thursday, May 23, 2019 | 6 | |
| Thursday, June 6, 2019 | 6 | |
| Thursday, June 20, 2019 | 9 | |
| Wednesday, July 3, 2019 | 17 | |
| Thursday, July 18, 2019 | 9 | |
| Thursday, August 1, 2019 | 20 | |
| Thursday, August 15, 2019 | 9 | |
| Thursday, August 29, 2019 | 18 | |
| Thursday, September 12, 2019 | 9 | |
| Thursday, September 26, 2019 | 13 | |
| Thursday, October 10, 2019 | 13 | |
| Thursday, October 24, 2019 | 11 | |
| Thursday, November 7, 2019 | 15 | |
| Thursday, November 21, 2019 | 19 | |
| Thursday, December 5, 2019 | 20 | |
| Thursday, December 19, 2019 | 22 | |
| Thursday, January 2, 2020 | 13 | |
| Thursday, January 16, 2020 | 17 | |
| Thursday, January 30, 2020 | 27 | |
| Wednesday, February 5, 2020 | 20 | |
| Thursday, February 27, 2020 | 13 | |
| Thursday, March 12, 2020 | 34 | |
| Sunday, March 29, 2020 | 23 |
Hi Anonymous ,
I create a sample. Please have a try to check if it is what you want.
- Create a year column
Year = YEAR('Table'[Date])- Create a measure
Measure = CALCULATE ( SUM ( 'Table'[Approved] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) && 'Table'[Year] = MAX ( 'Table'[Year] ) ) )
11 Replies
v-xuding-msftCommunity Support
Hi Anonymous ,
I create a sample. Please have a try to check if it is what you want.
- Create a year column
Year = YEAR('Table'[Date])- Create a measure
Measure = CALCULATE ( SUM ( 'Table'[Approved] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) && 'Table'[Year] = MAX ( 'Table'[Year] ) ) )Thank you, this solution was the simplest and it fit my case. I am amazed with how poeple are helpful here
- amitchandak
Super User
Anonymous , Looking at marked solution. You seems to have marked the message to wrong person 😀
Amazing, thank you!
- edhans
Community Champion
Read this fantastic article by SQLBI. It is about hiding future dates, but you can use the exact same concept. You just need a field in your Date table that is [IsCurrentYear] which just returns true or false if the year of the [Date] field is the same as the current year based on Today() if using a Calculated Column in DAX or DateTime.LocalNow() if using Power Query for your date table.
Then you just filter per that article on your IsCurrentYear field. I have one for the current year, quarter, week, month, etc for all sorts of easy measures and slicers.
By the way, you really need a true date table for this. See Creating a Dynamic Date Table in Power Query to create one in Power Query. You cannot add these fields to the automatic date table, which I can tell you are using based on your measure.
- amitchandak
Anonymous ,
You need to use YTD. YTD resets every year. Use the Date calendar with this
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ - ImkeF
Hi Anonymous
your formula should principally work as a measure. Or do you want to create a calculated column to your table?
Just be aware of the column you're referencing, as it is "[Approved During the 2 Week Reporting Period]" in your formula and plain "Approved" in the sample data you've given.
- edhans
Hi Anonymous - just curious, why do you want a calculated column? Is there a specific use case you are trying to satisfy?
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query
