cancel
Showing results for
Did you mean:
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:

 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?

Cor

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

9 REPLIES 9
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:

Can someone help me with the dax-formulas?

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

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

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

Super User

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

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

Regular Visitor

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

Hi Zubair,

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

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];

Super User

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

Try these MEASURE

```# 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

Highlighted
Regular Visitor

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

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?