cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corvada Regular Visitor
Regular Visitor

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

Hi everyone,

 

I have a dataset with the following invoices:

CustomerInvoiceInvoice_DateYear_InvoiceAmount_Excl_VATYMD-key
110011-3-201420142520140301
210025-6-201420144020140605
310039-9-201420143520140909
4100410-12-201420141220141210
110051-4-201520154520150401
210063-3-201520153820150303
310072-2-201520152720150202
510084-6-201520153020150604
610092-8-201520156020150802
710107-7-201520154020150707
8101115-10-201520153520151015
1101221-2-201620167320160221
210137-11-201620164520161107
5101415-7-201620166020160715
610152-4-201620166020160402
7101628-8-201620163820160828
9101714-1-201620161020160114
1010188-11-201620161520161108
11101925-11-201620162720161125
110201-4-201720179820170401
5102123-2-201720175820170223
710228-12-201720173620171208
9102324-12-201720172020171224
1010246-6-201720171220170606
1110251-6-201720172720170601
1210268-6-2017201725

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

9 REPLIES 9
corvada Regular Visitor
Regular Visitor

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

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

First and last.PNG

 

Can someone help me with the dax-formulas?

 

@Zubair_Muhammad

 

corvada Regular Visitor
Regular Visitor

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

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.... 

Super User
Super User

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

hi @corvada

 

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

Super User
Super User

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

@corvada

 

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 ) )

corvada.png

corvada Regular Visitor
Regular Visitor

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

@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:

 

Zubair.JPG

 

 

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 + YearNote
2014-Q1110 
2014-Q2220 
2014-Q3330 
2014-Q4440 
2015-Q1330Customer 1 is a lost client
2015-Q2532Customers 1 and 2 are in their second year

 

Do you understand and can you help me?

 

Regards,


Cor

 

 

corvada Regular Visitor
Regular Visitor

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

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))))

Super User
Super User

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

@corvada

 

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 )
Super User
Super User

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

corvada Regular Visitor
Regular Visitor

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

@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