Forum Discussion

lliu08's avatar
lliu08
Helper I
6 years ago
Solved

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...
Need Help
  • lliu08's avatar
    lliu08
    6 years ago

    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. 

parry2k's avatar
parry2k
Super User
6 years ago

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

 

  • lliu08's avatar
    lliu08
    Helper I
    6 years ago

    "

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