Cumulative Column Summation

[Big_Trucks]

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]

Awesome thanks for the fast replies. I will try and see if this works and get back with a result. 

[amitchandak]

@Big_Trucks ,

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


Appreciate your Kudos.

[Greg_Deckler · Accepted solution]

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

[Big_Trucks]

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.

[Daviejoe]

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

[Big_Trucks]

Hi @Daviejoe  I saw that post and unfortunelty, it did not provide me with the solution needed.

[Daviejoe]

I'd definitely go with the suggestion from @Greg_Deckler