Lost Customers

Anything related to PowerPivot and DAX Formuale
PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Lost Customers

Postby PhilC » Fri May 12, 2017 9:31 am

Hi all,

Am trying to modify the Lost Customer measure from DAX Patterns (http://www.daxpatterns.com/new-and-returning-customers/)

It returns the customers that are lost in the period displayed. I want to show customers in the period shown that become lost in the future.

Code: Select all

=COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            FILTER (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        VALUES ( <customer_key_column> ),
                        "CustomerLostDate",
                            CALCULATE ( MAX ( <fact_date_column> ) ) + [LostDaysLimit]
                    ),
                    FILTER (
                        ALL ( <date_table> ),
                        AND (
                            <date_column> < MIN ( <date_column> ),
                            <date_column>
                                >= MIN ( <date_column> ) - [LostDaysLimit]
                        )
                    )
                ),
                AND (
                    AND (
                        [CustomerLostDate] >= MIN ( <date_column> ),
                        [CustomerLostDate] <= MAX ( <date_column> )
                    ),
                    [CustomerLostDate]
                        <= CALCULATE ( MAX ( <fact_date_column> ), ALL ( <fact_table> ) )
                )
            ),
            "FirstBuyInPeriod", CALCULATE ( MIN ( <fact_date_column> ) )
        ),
        OR (
            ISBLANK ( [FirstBuyInPeriod] ),
            [FirstBuyInPeriod] > [CustomerLostDate]
        )
    )
)


[Sorry, the DAX format did not like this code]

My data is annual, so I have changed the code to use Year rather than Date, but I cannot get my head around how to return existing customers in the current year who do not come back the following year.

Cheers
Phil

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Lost Customers

Postby MattAllington » Fri May 12, 2017 9:47 am

This is hilarious. I literally did a modification for a client in Sweden on this exact measure at 5am this morning. Amazing. I can take a look but I am flat out atm. I will take a look if I get a few minutes over the weekend.

If you want to do some L&D yourself, my advice is to fire up DAX Studio and start decomposing the formula. Start from the inside out and build each piece of the puzzle so you can learn how it works. Don't expand the formula to the outer parts until you are 100% clear what each table delivers you. Does that make sense?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Lost Customers

Postby PhilC » Fri May 12, 2017 10:07 am

Will have a crack at that, thanks Matt.

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Lost Customers

Postby PhilC » Mon May 15, 2017 11:01 am

Hi Matt, I had a crack with this using DAX Studio (do not know why I have not used this before) but am still struggling, so any guidance you can provide would be appreciated.

Cheers
Phil

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Lost Customers

Postby PhilC » Thu May 25, 2017 11:21 am

Bump, any assistance with this would be appreciated.

Cheers
Phil

Oxenskiold
Posts: 30
Joined: Tue Jan 05, 2016 10:38 pm

Re: Lost Customers

Postby Oxenskiold » Thu May 25, 2017 7:35 pm

Hi Phil,

if I understand you right this measure should do the job. The EXCEPT function only works in DAX 2016 i.e. excel 2016, power bi and AS 2016. If you use an older version please let me know.

Code: Select all

=
COUNTROWS (
    EXCEPT (
        SUMMARIZE ( sales, customer[customerkey] ),
        CALCULATETABLE (
            SUMMARIZE ( sales, customer[customerkey] ),
            FILTER ( ALL ( calendar ), calendar[year] = MAX ( calendar[year] ) + 1 )
        )
    )
)


I don't know your data model so I use the column- and table names that come closest to a generic educational data model. If you don't have a seperate calendar table you can replace

FILTER ( ALL ( calendar ), calendar[year] = MAX ( calendar[year] ) + 1 )
with
FILTER ( ALL ( Sales[year]), Sales[year] = MAX ( Sales[year] ) + 1 ) where 'sales' is your fact table.

Best regards Jes.

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Lost Customers

Postby PhilC » Fri May 26, 2017 9:33 am

Hi Jes,

Thanks for that, at a quick glance it looks like it is doing what I am after, but will do some more testing. Thanks for taking the time. Will take me a while to understand how that expression is working, as SUMMARIZE seems to be quite a complex function.

Are you able to explain the FILTER where you put calendar[year] = MAX (calender[year]+1 - the concept of having the same field compared to itself confuses me (and is in the Lost Customers measure too)?

I will continue to unpack the original Lost Customers measure as I'd like to use some of the other measures as part of that.

Cheers
Phil


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest