New, recovered, returning and lost customers - using start and finish dates

[adamlang]

Hi there,

I'm implimenting the Dax "Pattern" at the link below for customer churn analysis:

https://www.daxpatterns.com/new-and-returning-customers/

I've copied the dax over to my data model and it seems to be working as expected.

I'm trying to implement the 'Generic dynamic pattern (dynamic by category)' Pattern, in a dataset that needs to reference two dates, - the later of a start date or a finish date. It's a client database for a charity that looks at enrolments onto a project (this can last for anything from a couple of days, months or in some cases more than a year). Maybe the more typical sales scenario might be when you want to look at order/purchase dates and a delivery date, and use the later of the pair of dates for the segmentation.

When an enrollment is currently active, there will only be a start date, and the finish date will be blank, in which case I'd need to just reference the start date. But when an enrollment is finished I'd like to reference that finish date, at the point from which a customer might become 'Lost', in my case a customer becomes lost when they haven't enrolled in the service for 12 months or more.

I'm fairly new to DAX/ Power BI, and struggling to work this out. Which measures do I need to tweak, and how to I get them to consider a pair of dates, and take to newer of the two?

By way of example,

Date Lost Client =

VAR MaxDate =

    CALCULATE (                    -- The last sale is the MAX of Order Date in the

        MAX ( pre_projectenrolment[pre_startdate] ), -- current time period (set by the calling measure)

        ALLEXCEPT (

            pre_projectenrolment,                                        -- ignoring any filter

            pre_projectenrolment[Contact.ccl3030_uniquecrmnumber],       -- other than Customer

            Contact,

            'pre_project (2)'[pre_service]    -- and Service Category

        )

    )

VAR Result =

    IF (

        NOT ISBLANK ( MaxDate ),        

        EOMONTH ( MaxDate, 12 )     -- Twelve months later (end of month)

    )

RETURN

    Result

As well as pre_projectenrolment[pre_startdate], I need this measure to be able to look at pre_projectenrolment[pre_enddate], and use the max/lastest/higher of the two for the MaxDate.

Thanks,

Adam

[adamlang]

Thanks @123abc that seems to be working at treat!

[123abc · Accepted solution]

Create a DAX measure that calculates the "Date Lost Client" based on either the "start date" or the "finish date" from your dataset, depending on whether a customer is currently active or not. You want to consider the maximum of these two dates and then add 12 months to determine when a customer becomes 'Lost.' To achieve this, you can modify your DAX measure as follows:

Date Lost Client =
VAR MaxStartDate = CALCULATE(
MAX(pre_projectenrolment[pre_startdate]),
ALLEXCEPT(
pre_projectenrolment,
pre_projectenrolment[Contact.ccl3030_uniquecrmnumber],
Contact,
'pre_project (2)'[pre_service]
)
)

VAR MaxEndDate = CALCULATE(
MAX(pre_projectenrolment[pre_enddate]),
ALLEXCEPT(
pre_projectenrolment,
pre_projectenrolment[Contact.ccl3030_uniquecrmnumber],
Contact,
'pre_project (2)'[pre_service]
)
)

VAR MaxDate = IF(
NOT ISBLANK(MaxEndDate),
MAX(MaxStartDate, MaxEndDate),
MaxStartDate
)

VAR Result = IF(
NOT ISBLANK(MaxDate),
EOMONTH(MaxDate, 12), -- Twelve months later (end of month)
BLANK()
)

RETURN Result

Here's what this modified DAX measure does:

1. It calculates the maximum "start date" (MaxStartDate) and the maximum "finish date" (MaxEndDate) for each customer and service combination.

2. It checks if the "finish date" is not blank. If the "finish date" is available, it chooses the maximum date between "start date" and "finish date." If the "finish date" is blank, it uses the "start date."

3. Finally, it calculates the date 12 months later from the chosen date and returns it as the "Date Lost Client." If neither date is available (both start and finish dates are blank), it returns a blank value.

This measure should provide you with the desired behavior of determining when a customer becomes 'Lost' based on the appropriate date (either start or finish) and adding 12 months to it.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[adamlang]

Hi All,

I've a new requirement for an additional segmentation pattern, related to the one above.

I'd like the Measures to refererence a different set of tables (not project enrollments as above) but this time - Appointments, Workshop Attendances, and Connection Zone Visits. They all have single dates (unlike project enrollment) and connect to the 'pre_project (2)' table in a similar way to Enrollments. The table names and column referecnes are as follows:

'Appointment' [Contact.ccl3030_uniquecrmnumber], [scheduledend] // the date field, 

'new_connectionzonevisits' [Contact.ccl3030_uniquecrmnumber]. [new_timearived] // the date field

'pre_workshopattendance' [Contact.ccl3030_uniquecrmnumber], [pre_workshopdate]

Basically I need the lost customer date to find the latest/most recent date across those three tables. I'm not sure would I have to modify the other measures as well - I think I can manage updating the other measures, but I'm not sure if they'd have anything different coding wise? I attach the list just in case.

Similarly Date First Customer would need to reference the first date across the three tables.

The previous measure is working fine (thanks so much @123abc), attached in drop box below are the versions of the measures I have incorporated into my power bi model, in text files. I'm sorry I don't have a demo .pbix file I can share so starting to think my model is becoming so complex I should start making a full set of demo data I can share.

https://www.dropbox.com/scl/fo/12m1gs4npxzbisijjqbxq/h?rlkey=kery2sxg15e5pzkofhanjxqt4&dl=0 

If someone could get me started on the Dax for one measure (say date lost cusomter) I think I can adapt it to the others.

Thanks so much for all the support.

Adam