DAX Workout 014 - Lost Customers

Paul.Gerber · May 4, 2023, 5:48pm

Welcome to another workout. This is a 3-star workout.

This will be a fun one. With the existing dataset, attempt to find the Lost Customers as shown and the revenue lost. Have fun !!

I am looking forward to the results and MAY THE FOURTH BE WITH YOU!!

Submission

Load the supplied data file into a new Power BI file, create your solution, and reply to this post. Upload a screenshot of your solution along with the DAX measure. Please format your DAX code and blur it or place it in a hidden section. You can do this by clicking on the “gear” icon above.

Period
This workout will be released on Thursday, May 4, 2023, and will end on Sunday, May 7, 2023. But you can always come back to any of the workouts and solve them.

DAX _14 Lost Customers.pbix (518.9 KB)

AlexisOlson · May 5, 2023, 2:56pm

I really can’t figure out the logic behind the numbers you’re showing. Can you define what lost revenue means more explicitly? For concreteness, maybe explain how Voolia Ltd has -159,259 lost revenue.

Are the dates relative to today or relative to the Month & Year slicer somehow?
Other than having purchased in a window, what makes the customer “lost”? Is there an unstated assumption that they haven’t purchased anything since 60 days ago as an additional condition?
Is the lost revenue calculated as revenue from the specific time window or all time or some sort of projection?
How is the country slicer intended to interact? If a customer makes purchases in one country and then switches to another, is that considered a “lost” customer for some slicer selections but not others?

Maybe these questions have obvious answers but I’m unable to read between the lines enough to make all of the necessary unstated assumptions to solve this.

HufferD · May 5, 2023, 9:14pm

Interesting problem. Nothing fancy with the visuals yet, but this is a screenshot (I think they are right :

With a “from” and “to” slicer controlling the min/max of these measuers

Lost Customers


Lost Customers = 
VAR __datesInPeriodLastSaleDaysFromStart = SELECTEDVALUE(Parameter__datesInPeriodRecentSaleLookback[Parameter__datesInPeriodRecentSaleLookback])
VAR __datesInPeriodLastSaleLookback = SELECTEDVALUE(Parameter__datesInPeriodLastSaleLookback[Parameter__datesInPeriodLastSaleLookback])
VAR __datesInPeriodRecentSaleDaysFromStart = 0
VAR __datesInPeriodRecentSaleLookback = SELECTEDVALUE(Parameter__datesInPeriodRecentSaleLookback[Parameter__datesInPeriodRecentSaleLookback])

VAR __pastCustomers =
    CALCULATETABLE(
        VALUES( Sales[Customer Name Index] )                 
        , FILTER( 
            ALL( 'Date' )
            , 'Date'[Date] > MIN( 'Date'[Date]) - __datesInPeriodLastSaleLookback
            && 'Date'[Date] < MIN( 'Date'[Date]) - __datesInPeriodLastSaleDaysFromStart 
        )
    )
VAR __recentCustomers =                                     
    CALCULATETABLE(
        VALUES( Sales[Customer Name Index])
        , FILTER( 
            ALL( 'Date' )
            , 'Date'[Date] > MIN( 'Date'[Date]) - __datesInPeriodRecentSaleLookback         
            && 'Date'[Date] < MIN( 'Date'[Date]) - __datesInPeriodRecentSaleDaysFromStart  
        )
    )
VAR Result = 
COUNTROWS(                                                   
    EXCEPT( 
        __pastCustomers    
        , __recentCustomers
    )
) 
// uncomment this line to return value aligned with example 
// * -1 -- a negative value for "how many customers were lost?" deoesn't seem right to me 
RETURN 
    Result

and

Lost Revenue

Lost Revenue = 
VAR __datesInPeriodLastSaleDaysFromStart = SELECTEDVALUE(Parameter__datesInPeriodRecentSaleLookback[Parameter__datesInPeriodRecentSaleLookback])
VAR __datesInPeriodLastSaleLookback = SELECTEDVALUE(Parameter__datesInPeriodLastSaleLookback[Parameter__datesInPeriodLastSaleLookback])
VAR __datesInPeriodRecentSaleDaysFromStart = 0
VAR __datesInPeriodRecentSaleLookback = SELECTEDVALUE(Parameter__datesInPeriodRecentSaleLookback[Parameter__datesInPeriodRecentSaleLookback])

VAR __pastCustomers =
    CALCULATETABLE(
        VALUES( Sales[Customer Name Index] )                 
        , FILTER( 
            ALL( 'Date' )
            , 'Date'[Date] > MIN( 'Date'[Date]) - __datesInPeriodLastSaleLookback
            && 'Date'[Date] < MIN( 'Date'[Date]) - __datesInPeriodLastSaleDaysFromStart 
        )
    )
VAR __recentCustomers =                                      -- virtual table of customers 
    CALCULATETABLE(
        VALUES( Sales[Customer Name Index])
        , FILTER( 
            ALL( 'Date' )
            , 'Date'[Date] > MIN( 'Date'[Date]) - __datesInPeriodRecentSaleLookback         
            && 'Date'[Date] < MIN( 'Date'[Date]) - __datesInPeriodRecentSaleDaysFromStart  
        )
    )
VAR Result = 
CALCULATE(
    CALCULATE(
        [Total Sales]
        , DATESBETWEEN(
            'Date'[Date]
            , MIN('Date'[Date]) - __datesInPeriodLastSaleLookback
            , MIN('Date'[Date]) - __datesInPeriodLastSaleDaysFromStart
        )
    )
    , EXCEPT( 
        __pastCustomers
        , __recentCustomers
    ) 
) 

RETURN 
    Result

The “clue” you provided about using except() directed me to this approach versus what I consider to be the more typical approach.

HufferD · May 6, 2023, 12:56am

For what it’s worth, I interpreted “lost revenue” simply as profit that could have been realized if we had retained the customer. It’s simplistic. More like an upper boundary. A better start is estimating potential profit in the missing time periods based on values during the non-missing periods.

I believe dates are relative to the order date. the goal is identifying customers that used to buy things from us but havent bought anything from us recently. this could help us regain “lost” revenues by prioritizing or focusing efforts on those that we lost with the highest potential yield. Like tailoring advertising material to those lost customers that could potentially provide the highest returns.

As far as I can tell, you’re right to assume “lost” means they bought from us during some period in the past, in this case, the period 365 days from the current date until 60 days from the current date, but haven’t purchased anything since, in this case, 60 days of the current date.

I think here lost revenue should be a considered a temporary, naive estimator and just a placeholder so to speak for a more sophisticated measure like what you have in mind. the DAX mechanics would be the same. You’d just replace the naive estimate with the model-based estimate. when i did it i didnt even make adjustments for differences in time periods (10-months vs. 2-months)…even less than naive estimate

I assumed the country slicer was intended just as a simple but practical example of accommodating slicer-measure-visual interactions. you pose an interesting and relevant complication regarding customers that are “lost” but reappear elsewhere. it would require we extend our basic approach and introduce more and more complications, like identifying new customers and new customers that are only new in the sense that they are new to that country but are lost in another. many others.

this is a pretty common scenario and it’s possible to easily layer more and more complicated conditions to draw deeper and deeper insights. I think the gist is getting the groundwork down so that those complicated layers aren’t too difficult to manage.

Paul.Gerber · May 6, 2023, 2:29am

This is awesome @HufferD Always enjoy seeing you on these workouts. I’m glad you are enjoying them.

Paul

AlexisOlson · May 6, 2023, 4:36am

From examining @HufferD’s code, I can fill in the assumptions I was missing.

Lost Customers

Lost Customers = 
VAR _SlicerDate = MIN ( 'Date'[Date] )
VAR _Start = _SlicerDate - 365 /*Can be a parameter*/
VAR _End   = _SlicerDate - 60  /*Can be a parameter*/
VAR _Customers_ =
    CALCULATETABLE (
        SUMMARIZE (
            Sales,
            Customers[Customer Index],
            "LastSale", MAX ( Sales[OrderDate] )
        ),
        'Date'[Date] < _SlicerDate
    )
VAR _LostCustomers_ =
    FILTER (
        _Customers_,
        [LastSale] > _Start && [LastSale] < _End
    )
VAR _Result = SUMX ( _LostCustomers_, 1 )
RETURN
    _Result

Revenue Lost

Same as above except replace

VAR _Result = SUMX ( _LostCustomers_, 1 )

with

VAR _Result =
    CALCULATE (
        [Total Sales],
        _LostCustomers_,
        DATESBETWEEN ( 'Date'[Date], _Start, _End )
    )

Hakeem · May 6, 2023, 4:48am

That PBIX file is pointing to someone’s local directory. How did you guys get access to the practice dataset file?

Paul.Gerber · May 6, 2023, 6:58pm

@Hakeem I’m using the PBIx file as I did for the Pareto and the bar chart (changing colors). You can just build on those. I want to keep it easy and reuse the same dataset.

Hakeem · May 7, 2023, 12:58am

Sounds good. Thanks!

Paul.Gerber · May 7, 2023, 3:08am

@Hakeem let me know and I’ll upload again

mbraun · May 8, 2023, 6:22pm

Took me a little bit to go from paper and concept to actual model & report…

Paul.Gerber · May 8, 2023, 6:32pm

Wow, @mbraun you went above and beyond for this workout. Excellent work!!

ilittel · May 10, 2023, 8:05am

hi paul . i got a little confused. i looked at the data and all 175 customers had orders in last month of sales. so how could we find lost customers if they already bought sth in last month of data?
sincerely mahdi

Paul.Gerber · May 10, 2023, 10:51pm

@ilittel thanks for the message. The DAX code should be customers that would be lost if they didn’t purchase a product within the last 60 days up to the last year. So if it’s 30 days since last purchase they are not considered “lost”.

Do you have a copy of the code?

ilittel · May 13, 2023, 8:12am

I got it. thanks paul. this is my approach. i know i had to use EXCEPT function . but this is all i got by my self. then i could use some cheat from @SamMcKay tutorials from past but here i am.
sincerely
mahdi safiri
DAX _14 Lost Customers.pdf (689.0 KB)
by the way since time of workout has been past so this is my DAX code:

lost customers = 
var StartDate='Start Date'[Start Date Value]
var EndDate='End Date'[End Date  Value]
VAR CustomersPurchase60 =
    CALCULATE (
        DISTINCTCOUNT ( Sales[Customer Name Index] ),
        Sales[OrderDate] < MAX ( 'Date'[Date] )
            && Sales[OrderDate]
                > MAX ( 'Date'[Date] ) - EndDate
    )
VAR PreviousCustomers =
    CALCULATE (
        DISTINCTCOUNT ( Sales[Customer Name Index] ),
        Sales[OrderDate]
            > MAX ( 'Date'[Date] ) - startdate
            && Sales[OrderDate]
                < MAX ( 'Date'[Date] ) - EndDate
    )
RETURN
    ( PreviousCustomers - customerspurchase60 ) * -1

lost customers  revenue =
VAR customerspurchase60 =
    CALCULATE (
        SUM ( Sales[Line Total] ),
        Sales[OrderDate] < MAX ( 'Date'[Date] )
            && Sales[OrderDate]
                > MAX ( 'Date'[Date] ) - 60
    )
VAR previouscustomer =
    CALCULATE (
        SUM ( Sales[Line Total] ),
        Sales[OrderDate]
            > MAX ( 'Date'[Date] ) - 365
            && Sales[OrderDate]
                < MAX ( 'Date'[Date] ) - 60
    )
RETURN
    ( previouscustomer - customerspurchase60 ) * -1

shahadathossen.shajj · May 19, 2023, 11:21am

Here is my submission, thanks mbraun for breaking down the definition, It helped me to standout these functions.

DAX Measures

This text will be hidden

Lost Customer =
VAR __recentCustomer =
    CALCULATETABLE (
        VALUES ( Sales[Customer Name Index] ),
        Sales[OrderDate] < MAX ( 'Date'[Date] )
            && Sales[OrderDate]
                > MAX ( 'Date'[Date] ) - SELECTEDVALUE ( 'End Range'[End Range] )
    )
VAR __previousCustomer =
    CALCULATETABLE (
        VALUES ( Sales[Customer Name Index] ),
        Sales[OrderDate]
            > MAX ( 'Date'[Date] ) - SELECTEDVALUE ( 'Start Range'[Start Range] )
            && Sales[OrderDate]
                < MAX ( 'Date'[Date] ) - SELECTEDVALUE ( 'End Range'[End Range] )
    )
VAR __result =
    COUNTROWS ( EXCEPT ( __previousCustomer, __recentCustomer ) )
RETURN
    - 1 * __result


Lost Customer Revenue =
VAR __recentCustomer =
    CALCULATE (
        [Total Sales],
        Sales[OrderDate] < MAX ( 'Date'[Date] )
            && Sales[OrderDate]
                > MAX ( 'Date'[Date] ) - SELECTEDVALUE ( 'End Range'[End Range] )
    )
VAR __previousCustomer =
    CALCULATE (
        [Total Sales],
        Sales[OrderDate]
            > MAX ( 'Date'[Date] ) - SELECTEDVALUE ( 'Start Range'[Start Range] )
            && Sales[OrderDate]
                < MAX ( 'Date'[Date] ) - SELECTEDVALUE ( 'End Range'[End Range] )
    )
RETURN
    - 1 * ( __previousCustomer - __recentCustomer )