Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Big_Trucks
Frequent Visitor

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

 

Big_Trucks_0-1596474686575.png

 

Thanks

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Big_Trucks
Frequent Visitor

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

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Memorable Member
Memorable Member

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.