Forum Discussion
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!
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.
6 Replies
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]) ) )"
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).
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
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.
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.
