Forum Discussion

TimPowerBI's avatar
TimPowerBI
Frequent Visitor
6 years ago
Solved

Creating a cumulative cost over time based on slicer filter

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

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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