powerbidaxcumulative-sumdatamodel

Power BI Cumulative Sum by Dimension Table

I want to cumulatively sum up cost:

I have a cost table

enter image description here

And a date table

enter image description here

They are connected via the DateKey.

I have written the following measure in the Cost Table:

Cumulative Total = 
CALCULATE (
SUM(Cost[Cost]),
FILTER (
ALL( Cost[Date] ),
'Cost'[Date] <= MAX (Cost[Date] )
)
)

The problem seems to be that I want to use the dimension table Date in the visual. When I use the Date from the cost table to sum up it works, but as soon as I use the Dates from the Date table, the result is sum and not cumulative.

enter image description here

Solution

  • Replace ALL( Cost[Date] ) with ALL(Cost)

    Cumulative Total = 
  CALCULATE(
    SUM(Cost[Cost]),
    FILTER( ALL(Cost), Cost[Date] <= MAX(Cost[Date]) ) 
  )