12 Month Calculation Total Based On Rolling Sales Totals - Client Churn Rate

[possible-possum]

I need some help with the following scenario:

A business wants to know how many clients it lost in a 12 month period. It is only interested in "high value clients" which are clients who have spent at least $50,000 across their entire lifetime.

The business defines a client as "churned" if they did not spend any money for 2 years. Due to this, we define the churn date as the date the client hasn't spent for 24 months, but they did spend 25 months ago. Here is my measure:

Count Churned Client High Value = 
SUMX(
    'Companies',
    IF(
        AND(
            [Churn_0_Total Sales 24 Months Static] = 0, -- spent nothing in the last 2 years
            AND(
                [Churn_0_Total Sales 25 Months Static] > 0, -- spent something in the last 2 years and 1 month
                [Churn_0_Total Sales Lifetime] >= 50000 -- spent at least $50000 ever
            )
        ),
        1,
        0
    )
)

So if we had Apr 2023 selected in a date slicer, any clients who had a spend of $0 since Apr 2021 but some spend since Mar 2021 would be considered churned in Apr 2023. And if they spent more than $50,000, they are "high value".

Management would like to see at a glance the total churn count for the past 12 months. How could I achieve this? For reference I define the total sales measures as follows (swapping 24 for 25 in the other measure, and no date filters on the lifetime measure). It allows for static calculation of historical sales (as opposed to a dynamic "last 24 months")

Churn_0_Total Sales 25 Months Static = -- last 24 month sales as per the latest selected date in the report
VAR LatestDate = MAX('Calendar'[Date])
VAR EarliestDate = EOMONTH(LatestDate, -24)+1
VAR Sales24Months =
CALCULATE(
    SUM(
        'Orders'[Price])
    ),

    'Orders'[Sale Date] >= EarliestDate,
    'Orders'[Sale Date] < LatestDate + 1
)
RETURN
Sales24Months

when summed manually, the total is 35. This is the result I want if I had Dec-23 selected in the report view:

MMM-YY	High Value Churned Per Month	Total Last 12 Months - option 1	Total Last 12 Months - option 2
Jan23		35
Feb23		35
Mar23		35
Apr23		35
May23		35
Jun23		35
Jul23		35
Aug23		35
Sep23		35
Oct23		35
Nov23		35
Dec23	6	35
TOTAL	6	35	35

I will only display the total in a card, so the emplty/repeating row values don't matter - we would only ever see the "35".

I suspect I may need to define some new total sales measures to get to what I need, but I am not quite sure what they need to be!

[v-kongfanf-msft]

Hi @possible-possum ,

According to your description, in general, business requirements related to rolling dates usually require the help of the DATESINPERIOD function. Together with a date slicer, you can easily time-solve data over a range of time.

DATESINPERIOD function (DAX) - DAX | Microsoft Learn

If the problem still exists, please provide some test data and screenshots and describe them.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly