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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Lost customers over years

Hello,

 

I am struggling with this problem of counting how many customers we lose every year.

 

Here you can see a simplified model of my data :

Data modelData modelSales TableSales TableCustomer TableCustomer Table

 

I tried some of the patterns explicited here : https://www.daxpatterns.com/new-and-returning-customers/#absolute-measures-for-new-/-lost-/-recovere....

 

Unfortunately, there is no LostDaysLimit in my case, the goal is only to calculate for each year these indicators :

  • New customers : customers with >= 1 purchase on a specific year y and without previous purchases
  • Reactivated customers : customers with >=1 purchase on a year y, no purchases year y-1, and >=1 purchases years y-n
  • Loyal customers : customers with >=1 purchase on year y, and >=1 purchase on year y-1
  • Attrition : customers with >=1 purchase on year y-1 and 0 purchase on year y

So far, I've been able to calculate New, Reactivated and Loyal customers using measures with these steps :

  • Add 2 calculated columns "Nb of sales per year" and "Previous Year Sales" in the Sales Table
Nb of sales per year = 
CALCULATE(
    COUNTA(Sales[Sales Amount]) + 0;
    FILTER(
        Sales;
        Sales[Customer ID] = EARLIER(Sales[Customer ID]) &&
        YEAR(Sales[Sales Date]) = YEAR(EARLIER(Sales[Sales Date]))
    )
)

Previous Year Sales = CALCULATE( MIN(Sales[Nb of sales per year]) + 0; FILTER( Sales; Sales[Customer ID] = EARLIER(Sales[Customer ID]) && Sales[Sales Year] = EARLIER(Sales[Sales Year]) -1 ) )

 

Reactivated customers := 
CALCULATE(
    [Nb of customers];
    FILTER(
        Sales;
        Sales[Nb of sales per year] > 0 &&
        Sales[Previous Year Sales] = 0 &&
        RELATED(Customer[Nb of purchases lifetime]) > Sales[Nb of sales per year] &&
        Sales[Sales Year] <> RELATED(Customer[Year of first purchase])
    )
)

Loyal customers :=
CALCULATE(
    [Nb of customers];
    FILTER(
        Sales;
        Sales[Nb of sales per year] > 0 &&
        Sales[Previous Year Sales] > 0
    )
)
 

These measures give me the desired results :

Results incompleteResults incomplete

 

But for the Nb of attrition measure, I'm really stuck on how to calculate it. So far, I've tried lots of solutions explicited on forums and blogs, but none of them gave me the desired results.

Nb of attrition := 
CALCULATE(
    [Nb of customers];
    FILTER(
        Sales;
        Sales[Previous Year Sales] > 0 &&
        Sales[Nb of sales per year] = 0
    )
)

I know the code above is very naïve and I understand why it doesn't work. Because as long as a customer has not made any purchase on a specific year, the [Nb of sales per year] column would not even exist as there is no row for this customer in the Sales table.

 

The desired result should look like this :

Desired resultsDesired results

 

Your help will be much appreciated.

 

Regards.

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous

 

try these measure and let me know, I haven't tested them.

 

You may also want to switch off the row total on the matrix or you'd need to add a condition in the dax formula to not return anything on the totals

 

New Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        AND( 
            CALCULATE( COUNTROWS( Sales ) ) > 0,
            CALCULATE( 
                COUNTROWS( Sales ),
                FILTER(
                    ALL( 'Date'[Year] ),
                    'Date'[Year] < MIN( 'Date'[Year] )
                )
            ) = 0
        )
    )
)
Reactivated Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year
    &&  CALCULATE(
            COUNTROWS( Sales ),
            FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 )
        ) = 0 -- no purchases last year
    && CALCULATE(
           COUNTROWS( Sales ),
           FILTER( ALL( 'Date'[Year] ), 'Date'[Year] < MAX( 'Date'[Year] ) - 1 )
       ) > 0 -- purchases before last year 
    )
)
Loyal Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year
    &&  CALCULATE(
            COUNTROWS( Sales ),
            FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 )
        ) > 0 --  purchases last year
    )
)    
Attrition Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        CALCULATE( COUNTROWS( Sales ) ) = 0 -- no purchases this year
    &&  CALCULATE(
            COUNTROWS( Sales ),
            FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 )
        ) > 0 --  purchases last year
    )
)    

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

7 REPLIES 7
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous

 

try these measure and let me know, I haven't tested them.

 

You may also want to switch off the row total on the matrix or you'd need to add a condition in the dax formula to not return anything on the totals

 

New Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        AND( 
            CALCULATE( COUNTROWS( Sales ) ) > 0,
            CALCULATE( 
                COUNTROWS( Sales ),
                FILTER(
                    ALL( 'Date'[Year] ),
                    'Date'[Year] < MIN( 'Date'[Year] )
                )
            ) = 0
        )
    )
)
Reactivated Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year
    &&  CALCULATE(
            COUNTROWS( Sales ),
            FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 )
        ) = 0 -- no purchases last year
    && CALCULATE(
           COUNTROWS( Sales ),
           FILTER( ALL( 'Date'[Year] ), 'Date'[Year] < MAX( 'Date'[Year] ) - 1 )
       ) > 0 -- purchases before last year 
    )
)
Loyal Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year
    &&  CALCULATE(
            COUNTROWS( Sales ),
            FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 )
        ) > 0 --  purchases last year
    )
)    
Attrition Customers = 
CALCULATE(
    COUNTROWS( Customer ),
    FILTER(
        VALUES( Customer[Customer Code] ),
        CALCULATE( COUNTROWS( Sales ) ) = 0 -- no purchases this year
    &&  CALCULATE(
            COUNTROWS( Sales ),
            FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 )
        ) > 0 --  purchases last year
    )
)    

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi, @LivioLanzo I have a similar setup and requirement, this solution works for me when I add it as a measure. 

But the result the measure provides does not filter the customer Table. is it possible to have a similar solution as Custom Colum?

 

Kind Regards

Anonymous
Not applicable

@LivioLanzoMy bad, it works on my dummy data but not on real data.

 

The 3 others are good but the Attrition one shows no data.

 

I thought that maybe the solution might be to calculate a new table with one row for every customers and each year that will calculate their yearly number of sales. But I have no clue on how to achieve that.

 

Thanks for your help.

Hi @Anonymous

 

Here are some posts for you to check if they could help you. If it is not your case, please share your data file or more details so that we could help further on it.

https://community.powerbi.com/t5/Desktop/Lost-Customers-Issues-with-DAX-Calculation/m-p/288380

http://radacad.com/lost-customers-dax-calculation-for-power-bi

 

Regards,

Cherie

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

Hi @Anonymous

 

as far as I know it should also work on your real data if the model is the same. We would need to see which customers should be included but are not, but without access to the real data I can't figure that out.

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

@LivioLanzoyou are right, it works. For other analysis purposes, I turned the bidirectional cross-filtering on between Customer and Sales. This stops the Attrition measure from working properly. But without it, it gives the correct results.

 

@LivioLanzoand @v-cherch-msft, thanks again for your help.

 

Regards.

 

Anonymous
Not applicable

@LivioLanzoThanks a lot ! It works like a charm.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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