Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
kjmts5200
Frequent Visitor

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

    )

)

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

vyiruanmsft_0-1722910248024.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
FreemanZ
Super User
Super User

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?

v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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



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?

vyiruanmsft_0-1722846584619.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft 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.

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

vyiruanmsft_0-1722910248024.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-yiruan-msft!!!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.