Count of customers first year versus count of customers not in first year

[Anonymous]

Hi everyone,

I have a dataset with the following invoices:

Customer	Invoice	Invoice_Date	Year_Invoice	Amount_Excl_VAT	YMD-key
1	1001	1-3-2014	2014	25	20140301
2	1002	5-6-2014	2014	40	20140605
3	1003	9-9-2014	2014	35	20140909
4	1004	10-12-2014	2014	12	20141210
1	1005	1-4-2015	2015	45	20150401
2	1006	3-3-2015	2015	38	20150303
3	1007	2-2-2015	2015	27	20150202
5	1008	4-6-2015	2015	30	20150604
6	1009	2-8-2015	2015	60	20150802
7	1010	7-7-2015	2015	40	20150707
8	1011	15-10-2015	2015	35	20151015
1	1012	21-2-2016	2016	73	20160221
2	1013	7-11-2016	2016	45	20161107
5	1014	15-7-2016	2016	60	20160715
6	1015	2-4-2016	2016	60	20160402
7	1016	28-8-2016	2016	38	20160828
9	1017	14-1-2016	2016	10	20160114
10	1018	8-11-2016	2016	15	20161108
11	1019	25-11-2016	2016	27	20161125
1	1020	1-4-2017	2017	98	20170401
5	1021	23-2-2017	2017	58	20170223
7	1022	8-12-2017	2017	36	20171208
9	1023	24-12-2017	2017	20	20171224
10	1024	6-6-2017	2017	12	20170606
11	1025	1-6-2017	2017	27	20170601
12	1026	8-6-2017	2017	25	20170608

For each quarter I want to calculate the count of customers who bought in their first year and I also want to count the customers who are not in their first year. For example, at the end of Q4-2014 there are 4 customers who are in their first year.

Can someone help me? 

Thanks in advance,

Cor

[Anonymous]

For each customer I've calculated the first en last day of the invoices:

Can someone help me with the dax-formulas?

@Zubair_Muhammad

[Anonymous]

I've tried it with the formula:

# First year =
COUNTROWS(
CALCULATETABLE(VALUES(Invoices[Customer]);
FILTER(Invoices;Invoices[Invoice_Date]-Invoices[First Invoicedate] <= 365)))

but it gave me the result for each quarter and I want the total of the customers of the last 12 months.... 

[Zubair_Muhammad]

hi @Anonymous

What is the end result you want with above sample data?

[Zubair_Muhammad]

@Anonymous

Please see if the attached file helps

I have used this MEASURE

FirstYearCustomerCount =
VAR selectedyear =
    SELECTEDVALUE ( Table1[Invoice_Date].[Year] )
VAR CurrentYearCustomer =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        ALLEXCEPT ( Table1, Table1[Invoice_Date].[Year] )
    )
VAR AllPreviousYearsCustomers =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        FILTER ( ALL ( Table1 ), Table1[Invoice_Date].[Year] < selectedyear )
    )
RETURN
    COUNTROWS ( EXCEPT ( CurrentYearCustomer, AllPreviousYearsCustomers ) )

[Anonymous]

@Zubair_Muhammad

Hi Zubair,

Thanks for your reply, I really appreciate it!

About the challenge, I had expected a different endresult. 

My challenge was to create churn-measures for each quarter, calculated for the last twelve months:

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

Now I want the Total Clients LTM separated in 2 parts, the number of 1st year customers and 2nd + year customers.

A part of the correct solution should be:

	Total Customers	# First Year	# Second + Year	Note
2014-Q1	1	1	0
2014-Q2	2	2	0
2014-Q3	3	3	0
2014-Q4	4	4	0
2015-Q1	3	3	0	Customer 1 is a lost client
2015-Q2	5	3	2	Customers 1 and 2 are in their second year

Do you understand and can you help me?

Regards,

Cor

[Anonymous]

The calculation of the customers of the prior period:

Total Clients Prior Period =
COUNTROWS(
CALCULATETABLE(VALUES(Invoices[Customer]);
DATESBETWEEN('Calendar'[Date];
DATEADD(STARTOFQUARTER('Calendar'[Date]);-7;QUARTER);
DATEADD(ENDOFQUARTER('Calendar'[Date]);-4;QUARTER))))

[Zubair_Muhammad]

@Anonymous

Try these MEASURE

Please see attached file

# First Year =
VAR mydate =
    MAX ( 'Calendar'[Date] )
VAR AllCustomers =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )
VAR CustomersforOneYear =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER )
    )
VAR Customersfor2Year =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        SAMEPERIODLASTYEAR (
            DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER )
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( CustomersforOneYear, Customersfor2Year ) )

Total Customers =
VAR mydate =
    MAX ( 'Calendar'[Date] )
VAR AllCustomers =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )
VAR CustomersforOneYear =
    CALCULATETABLE (
        VALUES ( Table1[Customer] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER )
    )
RETURN
    COUNTROWS ( CustomersforOneYear )

[Zubair_Muhammad · Accepted solution]

@Anonymous

[Anonymous]

@Zubair_Muhammad

Thanks for your reply and strange, it wasn't working for my dataset. I've sent you my pbix-file to your hotmail.

Please, can you take a look at the file and tell me what is going wrong?

Thanks in advance,

Regards,

Cor