cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Super User III
Super User III

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

9 REPLIES 9
Highlighted
Helper III
Helper III

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

 

Highlighted
Helper III
Helper III

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

Highlighted
Super User III
Super User III

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?

Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

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

Try my new Power BI game Cross the River
Highlighted
Helper III
Helper III

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

 

 

Highlighted
Helper III
Helper III

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

Highlighted
Super User III
Super User III

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 )
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

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

Highlighted
Helper III
Helper III

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

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors