cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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
1 ACCEPTED SOLUTION
Community 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] )
)
)
``````

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Community 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] )
)
)
``````

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

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

Anonymous
Not applicable

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

Super User

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

Anonymous
Not applicable

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

Super User

@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://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Anonymous
Not applicable

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

Super User

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors