cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

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

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?

Cor

1 ACCEPTED SOLUTION
Resolver I

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

7 REPLIES 7
Resolver I

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

Resolver I

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

Anonymous
Not applicable
Resolver I

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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors