cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
corvada Member
Member

Need help: Lost/New Customers each Year-Month

TestData:

CustomerIDStartEnd
11-1-20151-1-2020
21-7-20151-10-2016
31-12-20151-2-2018
41-2-20165-10-2016
51-8-20161-2-2019
61-5-20171-1-2020
71-11-20171-8-2018
81-4-20181-10-2019
925-7-201825-12-2018
1022-11-20181-1-2019
115-1-201931-3-2019
125-2-20191-1-2020
135-4-20195-6-2019
145-5-20191-1-2020
155-6-20191-1-2020

 

DataModel:

190625 Datamodel.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
copykai Frequent Visitor
Frequent Visitor

Re: Need help: Lost/New Customers each Year-Month

Hi again,

 

You had a small error in the measure Smiley Happy

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

7 REPLIES 7
copykai Frequent Visitor
Frequent Visitor

Re: Need help: Lost/New Customers each Year-Month

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.

 

Untitled.png

Best Regards

Kaj

corvada Member
Member

Re: Need help: Lost/New Customers each Year-Month

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 Frequent Visitor
Frequent Visitor

Re: Need help: Lost/New Customers each Year-Month

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.

corvada Member
Member

Re: Need help: Lost/New Customers each Year-Month

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

190625 Datamodel.pngformula.png

corvada Member
Member

Re: Need help: Lost/New Customers each Year-Month

copykai Frequent Visitor
Frequent Visitor

Re: Need help: Lost/New Customers each Year-Month

Hi again,

 

You had a small error in the measure Smiley Happy

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

corvada Member
Member

Re: Need help: Lost/New Customers each Year-Month

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