Forum Discussion

sparker22's avatar
sparker22
Frequent Visitor
3 years ago
Solved

Cumulative count based on relative date filter

I have a graph that using a relative date filter looks at the last 12 months. Is it possible to create a cumulative count that uses the same relative date filter, rather than including previous mont...
  • tamerj1's avatar
    tamerj1
    3 years ago

    sparker22 

    Please try

    Cumulative Total =
    VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
    VAR FirstDateWithData =
    CALCULATE ( MIN ( 'Outcome'[Date] ), REMOVEFILTERS () )
    RETURN
    CALCULATE (
    COUNT ( 'Outcome'[OutcomeID] ),
    'Calendar'[Date] >= FirstDateWithData,
    'Calendar'[Date] <= CurrentDate
    )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi sparker22 

In this case you need to edit the original measure code. Can you paste the code in a reply?

sparker22's avatar
sparker22
Frequent Visitor
3 years ago

This is the original code. The relative date filter is added on the visual level, rather than code.

 

Cumulative Total =
CALCULATE (
COUNT('Outcome'[OutcomeID]),
FILTER (
ALL( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    sparker22 

    Please try

    Cumulative Total =
    VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
    VAR FirstDateWithData =
    CALCULATE ( MIN ( 'Outcome'[Date] ), REMOVEFILTERS () )
    RETURN
    CALCULATE (
    COUNT ( 'Outcome'[OutcomeID] ),
    'Calendar'[Date] >= FirstDateWithData,
    'Calendar'[Date] <= CurrentDate
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks, this worked!!