cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

DateApprovedCumm.approved
Thursday, January 3, 20194 
Thursday, January 17, 20191 
Thursday, January 31, 20197 
Thursday, February 14, 20196 
Thursday, February 28, 20199 
Thursday, March 14, 201913 
Thursday, March 28, 201919 
Thursday, April 11, 20199 
Thursday, April 25, 201910 
Thursday, May 9, 201916 
Thursday, May 23, 20196 
Thursday, June 6, 20196 
Thursday, June 20, 20199 
Wednesday, July 3, 201917 
Thursday, July 18, 20199 
Thursday, August 1, 201920 
Thursday, August 15, 20199 
Thursday, August 29, 201918 
Thursday, September 12, 20199 
Thursday, September 26, 201913 
Thursday, October 10, 201913 
Thursday, October 24, 201911 
Thursday, November 7, 201915 
Thursday, November 21, 201919 
Thursday, December 5, 201920 
Thursday, December 19, 201922 
Thursday, January 2, 202013 
Thursday, January 16, 202017 
Thursday, January 30, 202027 
Wednesday, February 5, 202020 
Thursday, February 27, 202013 
Thursday, March 12, 202034 
Sunday, March 29, 202023 
1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
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] )
    )
)

5.PNG

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.

View solution in original post

10 REPLIES 10
v-xuding-msft
Community Support
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] )
    )
)

5.PNG

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.

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

Anonymous
Not applicable

@v-xuding-msft 

 

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

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Anonymous
Not applicable

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

 

amitchandak
Super User
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://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/



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
edhans
Super User
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 I answer your question? Mark my post as a solution!
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
ImkeF
Super User
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!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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 I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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