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 ,

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!!!