Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to Solution.
Hi @Anonymous ,
I create a sample. Please have a try to check if it is what you want.
Year = YEAR('Table'[Date])
Measure =
CALCULATE (
SUM ( 'Table'[Approved] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Year] = MAX ( 'Table'[Year] )
)
)
Hi @Anonymous ,
I create a sample. Please have a try to check if it is what you want.
Year = YEAR('Table'[Date])
Measure =
CALCULATE (
SUM ( 'Table'[Approved] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Year] = MAX ( 'Table'[Year] )
)
)
This is excellent! You just solved my problem, as well! Thank you. 😉
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 😀
@amitchandak You are right 😛 and I want to take the chane to thank you also for your response
@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/
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
111 | |
59 | |
57 |