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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lliu08
Helper I
Helper I

Cumulative Total Dax

Hello community! 

 

I have been stuck with trying to do the cumulative value for a loooonnnggg time, please help! 

 

I have a table with 3 columns, Item_ID, Accounting_Date and Costs. I am trying to do a cumulative total costs for each Item_ID base on the select date. So a user will select a date then the measure calculate the total from the beginning of time upto the selected date. 

 

I have tried using a measure Cumulative Total Costs = Calculate(SUM(Table_Name[Costs], FILTER(ALL(Table_Name), Table_Name[Accounting_Date] <= Earlier (Table_Name[Accounting_Date])))

 

This formula is giviing me issue where the last part Earlier (Table_Name[Accounting_Date]) --> it says cannot find the Accounting_Date Column

 

I have also tried Cumulative Total Costs = CALCULATE(SUM(Table_Name[Costs], FILTER(ALL (TABLE_NAME[Accounting_Date]), Table_Name[Accounting_Date] <= MAX(Table_Name[Accounting_Date])) 

 

with this formula, I am having problem in the very last part again, where the error says the parameter is not correct. 

 

Please help!! Really appreciate it! THANK YOU! 

1 ACCEPTED SOLUTION
lliu08
Helper I
Helper I

I came up with the solution that solved my problem 🙂 

 

CALCULATE(SUM(Costs),GROUPBY (Table_Name,Table_Name[Item_ID]),FILTER(ALL(Table_Name), Accounting_Date <= Accounting_Date)))

 

 

Thank you everyone who helped to answer my question. 

View solution in original post

6 REPLIES 6
lliu08
Helper I
Helper I

I came up with the solution that solved my problem 🙂 

 

CALCULATE(SUM(Costs),GROUPBY (Table_Name,Table_Name[Item_ID]),FILTER(ALL(Table_Name), Accounting_Date <= Accounting_Date)))

 

 

Thank you everyone who helped to answer my question. 

Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table and build a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  Create a slicer from the Date column of the Calendar Table and select any date there.  Write these measures

Total costs = SUM(Table_Name[Costs]

Total costs since inception = CALCULATE([Total costs],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

I think in both formula sum is not closing properly

CALCULATE(SUM(Table_Name[Costs]), FILTER(ALL (TABLE_NAME[Accounting_Date]), Table_Name[Accounting_Date] <= MAX(Table_Name[Accounting_Date]))

 

Try that.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

"

I think in both formula sum is not closing properly

CALCULATE(SUM(Table_Name[Costs]), FILTER(ALL (TABLE_NAME[Accounting_Date]), Table_Name[Accounting_Date] <= MAX(Table_Name[Accounting_Date]))

 

Try that."

 

The cumulative value doesn't seem to work. It is only doing the sum of the selected month instead all data <= selected month 

parry2k
Super User
Super User

@lliu08 usually you should have date dimension in your model for these kind of calculations, but change your code as below and see if it works

 

Cumulative Total Costs =
CALCULATE(
SUM(Table_Name[Costs]), 
FILTER(
ALLSELECTED (TABLE_NAME), 
Table_Name[Accounting_Date] <= MAX(Table_Name[Accounting_Date])
)
) 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

"

Cumulative Total Costs =
CALCULATE(
SUM(Table_Name[Costs]), 
FILTER(
ALLSELECTED (TABLE_NAME), 
Table_Name[Accounting_Date] <= MAX(Table_Name[Accounting_Date])
)

 

The formula does not have any errow however, all the items are getting the same cumulative values. 

 

For additional information, in the final table that is displayed on PowerBI, 

 

I have "Item with Description", Cumulative Costs, the cumulative cost is the measure I am trying to create and the "Item with Description" is a relationship I made within in powerBI. I can get the Item with Description from another table (the join is on the item_id). 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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