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 month totals or using a fixed date filter which will require changing each month?

i.e. not this 

 

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

8 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    Dhi sparker22 ,

    try to write a measure like:

    CumCount =

    VAR _date = MAX(TableName[Date])

    RETURN

    CALCULATE(

        COUNT(TableName[ColumnOfInterest]),

        TableName[Date] <= _date

    )

    • sparker22's avatar
      sparker22
      Frequent Visitor
      3 years ago

      Hi FreemanZ 

      Appears to be counting backwards, and still including totals outside the relative date range

       

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