12 Month Running total with Filter

[kjmts5200]

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 should show the distinct number of active clients from July 2023 through June 2024, but currently, it includes clients who may have closed and are no longer active in June. Is it possible to include only the clients that are still active within that calendar month in the running total?

Total Clients 12 Month Running Total =

VAR CurrentDate = MAX('Calendar'[Month])

RETURN

CALCULATE(

    [Total Clients],  

         DATESINPERIOD(

        'Calendar'[Month],

        MAX('Calendar'[Month]),

        -12,

        MONTH

    )

)

[FreemanZ]

hi @kjmts5200 ,

just change 12 to 1?

As, the intersection of "clients active in the last 12 month" and "clients active in the last month" is "clients active in the last month". or?

[Anonymous]

Hi @kjmts5200 ,

Base on your description, it seems like you want to get 12 months running totals of Active clients. Could you please provide some raw data in your fact table (exclude sensitive data) with Text format and your expected result with calculation logic(conditions) of active clients base on the shared sample data? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[kjmts5200]

Thank you for your assistance. Below is a link to a PBIX file with sample data. In the table called “table,” there are the following columns:

1. Client ID - The unique identifier for each client.
2. Client Tiers Monthly - The revenue tier a client is placed into on a monthly basis based on the revenue generated that month.
3. Calendar Month - The month for which the data is recorded.
4. Highest Tier Last 12 Months - The highest tier the client has reached either in the current month or the previous 12 months.

The result I am trying to achieve with the rolling 12-month measure should equal the distinct number of Client IDs that have “1. High-Value” listed in the “Highest Tier Last 12 Months” column.

The issue I am facing is that the measure I created for the running total over 12 months is including clients who are no longer active in the table for that particular month. For example, if John Doe was “1. High-Value” for January, the measure includes him in the totals for the next 12 months, which is fine only if he is listed in “table” for that calendar month. If he is not active for that month, he shouldn’t be included. This discrepancy is why the total using the calculated column is different from the total using the 12-month running total measure.

I cannot simply use the calculated column because the report I am creating is dynamic.

Lastly, the visual table I created shows the difference between the 12-month running total measure and the total using the calculated column, which is the desired result.

Thank you again for taking the time to review this. This issue has been quite challenging for me 😊

https://drive.google.com/file/d/1pBAwAthm0dKhU3zLr5hipE7suIAC8lUG/view?usp=sharing

[Anonymous]

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]

@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 · Accepted solution]

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]

Thank you @Anonymous!!!