Cumulative sum that resets every year

[Anonymous]

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

[v-xuding-msft · Accepted solution]

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] )
    )
)

[Anonymous]

This is excellent!  You just solved my problem, as well!  Thank you.  😉

[Anonymous]

@v-xuding-msft 

Thank you, this solution was the simplest and it fit my case. I am amazed with how poeple are helpful here

[amitchandak]

@Anonymous , Looking at marked solution. You seems to have marked the message to wrong person 😀

[Anonymous]

@amitchandak  You are right 😛 and I want to take the chane to thank you also for your response

[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/

[edhans]

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.

[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.

[Anonymous]

Thanks Imek, 

I want a calculated column, the formula was for the original case but I forgot to change the column name when I prepared the test case to post here. Still didn't work

[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