Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

View solution in original post

7 REPLIES 7
copykai
Resolver I
Resolver I

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

Anonymous
Not applicable

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

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
Not applicable

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

190625 Datamodel.pngformula.png

Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.