cancel
Showing results for
Did you mean:
Highlighted
Member

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

Accepted Solutions
Frequent Visitor

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

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

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

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

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

Frequent Visitor

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

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.

Member

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

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

Member

Frequent Visitor

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

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

Member

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

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