Creating a cumulative cost over time based on slicer filter

[TimPowerBI]

Hi all,

I am trying to create a graph of cumulative cost over time based on what project is selected by the user on the dashboard.

Project	Date	Cost
A	20/01/2020	10.00
B	20/01/2020	20.00
A	20/01/2020	30.00
B	21/01/2020	10.00
A	21/01/2020	20.00
B	22/01/2020	20.00
A	23/01/2020	10.00
B	23/01/2020	20.00
A	23/01/2020	20.00

I have created this dummy data for show. 

I have been trying to use DAX to created a cumulative cost column but the problems Ive been having are:

• using something like CumulativeCost = CALCULATE(SUM(Sheet1[Cost]), all(Sheet1), Sheet1[Date] <= EARLIER(Sheet1[Date])) gives the right cumulative cost for both projects but not for just one.
• Using it with a filter: 
  CumulativeCost = CALCULATE(SUM(Sheet1[Cost]), all(Sheet1), Sheet1[Date] <= EARLIER(Sheet1[Date]), Sheet1[Project] = EARLIER(Sheet1[Project]))

  creates the right sums for the individual filters and works if the project A and B are on the same day (because theyll be summed) but if they aren't then itll only show the cumulative cost for that project on that day and not total (obviously).

  Is there any way to have the cost summed up for each day based on the filters that are selected from the dashboard?

  I am new to DAX so all help is welcomed! 

  Thanks 

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  To your visual/filter, drag the Project column from the Data Table and select any one project.  Write these measures

Total cost = SUM(Data[Cost])

Total cost till date = Calculate([Total cost],datesbetween(Calendar[Date],minx(all(Calendar[Date]),Calendar([Date])),min(calendar[Date])))

Hope this helps.

[danextian · Accepted solution]

Hi @TimPowerBI ,

The solution will depend on how your data model is setup and can be achieved by using either a  column or a measure.
 Sol # 1: using a separate Date table.

Cumulative Sum Measure =
CALCULATE (
    SUM ( 'Fact'[Column] ),
    FILTER ( ALL ( 'Dates'[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) )
)

Sol # 2 :  no separate Dates table.

Cumulative Sum Measure =
CALCULATE (
    SUM ( 'Fact'[Column] ),
    FILTER ( ALL ( 'Fact'[Date] ), 'Fact'[Date] <= MAX ( 'Fact'[Date] ) )
)

 Sol # 3: as a calculated column.

Cumulative Sum Column = 
CALCULATE (
    SUM ( 'Fact'[Column] ),
    FILTER (
        ALL ( 'Fact' ),
        'Fact'[Date] <= EARLIER ( 'Fact'[Date] )
            && 'Fact'[Category] = EARLIER ( 'Fact'[Category] )
    )
)

[mahoneypat]

Instead of a calculated column, this is better done with a measure like the one below.  It will work in your visual whether Project is in it or not.  For example, make a matrix with date on the rows and project on the column, with this measure in the values.

Cumulative Cost =
VAR __maxdate =
    MAX ( Cost[Date] )
RETURN
    CALCULATE (
        SUM ( Cost[Cost] ),
        ALLSELECTED ( Cost ),
        VALUES ( Cost[Project] ),
        Cost[Date] <= __maxdate
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat