# Lost/New Customers each Year-Month

Hi everyone,

In the past I’ve been busy with the Customer Churn but it seems to be that I’ve forgotten some essential parts… Can someone help me with a formula?

TestData:

CustomerID Start End
1 1-1-2015 1-1-2020
2 1-7-2015 1-10-2016
3 1-12-2015 1-2-2018
4 1-2-2016 5-10-2016
5 1-8-2016 1-2-2019
6 1-5-2017 1-1-2020
7 1-11-2017 1-8-2018
8 1-4-2018 1-10-2019
9 25-7-2018 25-12-2018
10 22-11-2018 1-1-2019
11 5-1-2019 31-3-2019
12 5-2-2019 1-1-2020
13 5-4-2019 5-6-2019
14 5-5-2019 1-1-2020
15 5-6-2019 1-1-2020

Measures:

``````# Customers = DISTINCTCOUNT(Testdata[Customer_ID])

Active Customers = CALCULATE([# Customers];

FILTER(Testdata; Testdata[Start] <= MAX('Calendar'[Date]));

FILTER(Testdata; Testdata[End] >= MIN('Calendar'[Date])))

First Day Start = FIRSTDATE(Testdata[Start])

Last Date LM = EOMONTH(MAX('Calendar'[Date]);-1)

LostCustomers =

CALCULATE([# Customers];

FILTER(Testdata; Testdata[End] <= MAX('Calendar'[Date])))

NewCustomersTotal =

CALCULATE([# Customers];

FILTER(Testdata; Testdata[Start] <= MAX('Calendar'[Date])))
``````

I would like to have the following endresult:

Year-Month New Lost
2015-01 1
2015-02
2015-03
2015-04
2015-05
2015-06
2015-07 1
2015-08
2015-09
2015-10
2015-11
2015-12 1
2016-01
2016-02 1
2016-03
2016-04
2016-05
2016-06
2016-07
2016-08 1
2016-09
2016-10 2

I know I have to work with two calculatetables:

1. Current Customers: from First Day Start until Last Date Current Period = MAX(Calendar[Date]

2. Prior Customers: from First Day Start until Last Date Prior Period

and then countrows(except(Current Customers, Prior Customers) but I don’t know exactly how I can write the formula. Can somebody help me?

Cor

One of the solutions:

``````New2 = CALCULATE(COUNTROWS(Testdata);

FILTER(Testdata; Testdata[Start] >= min('Calendar'[Date]));

FILTER(Testdata; Testdata[Start] <= max('Calendar'[Date])))

Lost2 = CALCULATE(COUNTROWS(Testdata);

FILTER(Testdata; Testdata[End] >= min('Calendar'[Date]));

FILTER(Testdata; Testdata[End] <= max('Calendar'[Date])))*-1``````

Another solution:

``````Test New = CALCULATE(DISTINCTCOUNT(Testdata[Customer_ID]); USERELATIONSHIP(Testdata[Start];'Calendar'[Date]))

Lost New = CALCULATE(DISTINCTCOUNT(Testdata[Customer_ID]);USERELATIONSHIP(Testdata[End];'Calendar'[Date]))
``````

I am still curious of the solution with CALCULATETABLE…

Hi Cor,

Your dataset is a bit different here compared to many examples I’ve run on this.

I think you are pretty close actually to getting it by having a look through your formulas.

The calcs are actually not that difficult when you have data set up like this.

You don’t need CALCULATETABLE here because you are dealing with multiple dates in your fact table.

In all the other examples this wasn’t the case. So with the data being completely different, the DAX functions will be also.

I believe these are all you need

``````Active Customers = CALCULATE([# Customers],
FILTER(Testdata, Testdata[Start] <= MAX('Calendar'[Date])),
FILTER(Testdata, Testdata[End] >= MIN('Calendar'[Date])))

New Customers =
CALCULATE([# Customers],
FILTER( Testdata,
Testdata[Start] <= MIN('Calendar'[Date]) ) )

Lost Customers =
CALCULATE([# Customers],
FILTER( Testdata, Testdata[End] <= MAX('Calendar'[Date])))
``````

Remember also when using USERELATIONSHIP to reference the date columns only.

See below

With this function you’re attempting to virtually join these tables up with a relationship so it needs to be like for like columns.

So in summary this was all about dealing with multiple dates in your fact table.

Here’s some recent tutorials I created around this as well.

Thanks
Sam