Need help: Lost/New Customers each Year-Month

[Anonymous]

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

DataModel:

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?

Thanks in advance,

Cor

[copykai]

Hi corvada,

You need to add measures like these:

New = CALCULATE(DISTINCTCOUNT(Customer[CustomerID]),USERELATIONSHIP(Customer[Start],'Date'[Yearmonth]))

Lost = CALCULATE(DISTINCTCOUNT(Customer[CustomerID]),USERELATIONSHIP(Customer[End],'Date'[Yearmonth]))

Replace tablename Customer with Testdata and Date with Calendar.

Then you should be good to go.

Best Regards

Kaj

[Anonymous]

Thanks Kaj,

I've tried using the method you mentioned. But I get this error: USERELATIONSHIP function can only use the two columns references participating in relationship

[copykai]

Hi corvada,

Are you then sure that you are referencing the two inactive relationships in your data model?

I can see that you have two relationships and of course these should be the ones used in the measure.

[Anonymous]

Yes, kaj, I haven't changed the Datamodel (see photo):

[Anonymous]

https://www.dropbox.com/s/9ux3r60pfhiahbi/Testfile%20Calculated%20Table.pbix?dl=0

Here is my file

[copykai · Accepted solution]

Hi again,

You had a small error in the measure 🙂

USERELATIONSHIP(Testdata[End],'Calendar'[Year-Month]))

Should be:

USERELATIONSHIP(Testdata[End],'Calendar'[Date]))

This is the issue in both cases.

The USERELATIONSHIP can only use the two columns that has an already active or inactive relationship.

Best Regards

Kaj

[Anonymous]

Thank you very much, Kaj, I really appreciate your help!