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 
Please try

Cumulative Measure 2 =
IF (
    NOT ISEMPTY ( FactTable ),
    [Cumulative Measure]
)
sparker22's avatar
sparker22
Frequent Visitor
3 years ago

Hi tamerj1 

This succeeds in removing the line on the graph on irrelevant months, but still totals the previous irrelevant months figures in the first relevant month

 

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