Forum Discussion

kjmts5200's avatar
kjmts5200
Frequent Visitor
2 years ago
Solved

12 Month Running total with Filter

Hello. I have the DAX formula below, which calculates a 12-month running total of clients. However, I need this measure to filter only for active clients in the current month. For example, June shoul...
"DAX help"
Running Totals
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi kjmts5200 ,

    Thanks for your reply. You can create a measure as below to get it:

    Desired result = 
VAR _selmonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR _client1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Client ID] ),
        FILTER (
            'Table',
            'Table'[Month]
                >= EOMONTH ( _selmonth, -13 ) + 1
                && 'Table'[Month] <= _selmonth
                && 'Table'[Highest Tier Last 12 Months] = "1. High-Value"
        )
    )
VAR _client2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Client ID] ),
        FILTER ( 'Table', 'Table'[Month] = _selmonth )
    )
RETURN
    COUNTROWS ( INTERSECT ( _client1, _client2 ) )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi kjmts5200 ,

I have some doubt about your requirement. For example: Client ID 18, we need to count it from June 2023 to May 2024 since the value of field [Highest Tier Last 12 Months] as "1. High-Value"? Or will count this client only on June 2023?

Best Regards

kjmts5200's avatar
kjmts5200
Frequent Visitor
2 years ago

Anonymous Thank you for taking the time to review this request.  Client 18 would be considered high-value for June 2023 to May 2024 excluding December 2023 (since they were not active that month i.e exlcuded from the table).  Hopefully that makes sense.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kjmts5200 ,

    Thanks for your reply. You can create a measure as below to get it:

    Desired result = 
VAR _selmonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR _client1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Client ID] ),
        FILTER (
            'Table',
            'Table'[Month]
                >= EOMONTH ( _selmonth, -13 ) + 1
                && 'Table'[Month] <= _selmonth
                && 'Table'[Highest Tier Last 12 Months] = "1. High-Value"
        )
    )
VAR _client2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Client ID] ),
        FILTER ( 'Table', 'Table'[Month] = _selmonth )
    )
RETURN
    COUNTROWS ( INTERSECT ( _client1, _client2 ) )

    Best Regards

    • kjmts5200's avatar
      kjmts5200
      Frequent Visitor
      2 years ago

      Thank you Anonymous!!!