# of 1st year customers vs # of 2nd + year customers

Power BI
1

Hi Fellow Power BI Users -

I’ve sent you an e-mail with the pbix-file of the quarterly churn-challenge. I’ve solved that part of the challenge.
I would like your help with the part of the number of 1s year customers vs the number of 2nd + year customers.

Quarterly%20Churn%20comparion
Quarterly Churn comparion.JPG1042×357 62.8 KB

I’ve calculated for each customer the first invoice and the last invoice:

Invoices etc..JPG1357×705 140 KB

At the end I want for each quarter the total of the customers who are still in their first year and the total of the customers who are in their second etc. year.

Can you help me?

Thanks in advance,

Cor

2

With other words, we’ve calculated

Total Clients LTM = 
CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
    DATESBETWEEN('Calendar'[Date];
        NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])));
        LASTDATE('Calendar'[Date])))

How can I split this total in 1-st year and 2-years + customers?

The calculation for 1-st year customers is Calendar[Date] - Invoices[First Invoice Date] has to be <= 365/366 days. How can I add this calculation to the DAX-formula?

3

Ok let walk through this one as there’s a bit to it I think.

So you are looking to, in any quarter assess how many clients are still in there first year as a customer?

First make sure your context comes from the date table. I switch it around. The following formula wouldn’t work otherwise.

image

Here are my formula idea for you.

1st Year Customers = 
VAR CustomerSet1Year = CALCULATETABLE( VALUES( Invoices[Customer] ),
                    FILTER( ALL( 'Calendar' ),
                         'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] >= MAX( 'Calendar'[Date] ) - 365 ) )
VAR CustomerSetPrior1Year = CALCULATETABLE( VALUES( Invoices[Customer] ),
                    FILTER( ALL( 'Calendar' ),
                         'Calendar'[Date] <= MAX( 'Calendar'[Date] ) - 365 ) )


RETURN
COUNTROWS(
    EXCEPT( CustomerSet1Year, CustomerSetPrior1Year ) )

This compare last year customers to any time period prior. Only returning the ones who have purchased in the last year and not before that time.

2nd + Year Customers = 
VAR CustomerSet1Year = CALCULATETABLE( VALUES( Invoices[Customer] ),
                    FILTER( ALL( 'Calendar' ),
                         'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] >= MAX( 'Calendar'[Date] ) - 365 ) )
VAR CustomerSetPrior1Year = CALCULATETABLE( VALUES( Invoices[Customer] ),
                    FILTER( ALL( 'Calendar' ),
                         'Calendar'[Date] <= MAX( 'Calendar'[Date] ) - 365 ) )


RETURN
COUNTROWS(
    EXCEPT( CustomerSetPrior1Year, CustomerSet1Year ) )

This does something similar just in reverse.

Here are the answers. You’ll have to check if this works out as you expect

image

Attached
180905 Churn per kwartaal LTM TWS (1).pbix (539.0 KB)

4

Excellent Sam, thank you very much. It took me a while to understand the part

'Calendar'[Date] <= MAX( 'Calendar'[Date] ) && 'Calendar'[Date] >= MAX( 'Calendar'[Date] ) - 365 )

But I think I got it now.

5

Ok great