Forum Discussion
Cumulative Column Summation
Hi All,
I am having trouble getting my Dax to calculate for what I need. The values need to be in a column, in order to line up with other items in my table for filter calculations. I have tried the common formula listed below with no success.
Cumulative sales =
CALCULATE (
SUM ( 'YourTable'[Sales] ),
ALL ( 'YourTable)' ),
'YourTable'[Date] <= EARLIER ( 'YourTable'[Date] )
)
I added a screenshot of my table below. The Quarterly sum expected should be a summation of the quarterly budget values (which is a measure). The closest I have been able to come is from the following code
***Quarterly SUM Expected Cumulative =
CALCULATE(
SUMX (
SUMMARIZE (
'Table1',
'Table1'[** Budget Utilization]
),
[** Quarterly Budget]
),FILTER(ALL('Date Table'),'Date Table'[Year QTR] <= MAX('Date Table'[Year QTR]))
)
Thanks
Big_Trucks -
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.That said, you might try this:
***Quarterly SUM Expected Cumulative = VAR __Dates = FILTER(ALL('Date Table'),'Date Table'[Year QTR] <= MAX('Date Table'[Year QTR])) SUMX ( SUMMARIZE ( FILTER('Table1',[Date] IN __Dates), 'Table1'[** Budget Utilization] ), [** Quarterly Budget] )
7 Replies
Unless you want a reset at qtr it should work like this date example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))for QTD
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
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/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Big_Trucks -
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.That said, you might try this:
***Quarterly SUM Expected Cumulative = VAR __Dates = FILTER(ALL('Date Table'),'Date Table'[Year QTR] <= MAX('Date Table'[Year QTR])) SUMX ( SUMMARIZE ( FILTER('Table1',[Date] IN __Dates), 'Table1'[** Budget Utilization] ), [** Quarterly Budget] )This ended up working out with slight modification to my referances. One thing to note for others the 'Return' was not in the formula and needed to be added. Thanks for the help.
Hi Big_Trucks
spotted this earlier solution - let me know if it suits your needs
https://community.powerbi.com/t5/Desktop/Cumulative-Total/m-p/43358#M16684
Hi Daviejoe I saw that post and unfortunelty, it did not provide me with the solution needed.
I'd definitely go with the suggestion from Greg_Deckler
Awesome thanks for the fast replies. I will try and see if this works and get back with a result.
