Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Yes, kaj, I haven't changed the Datamodel (see photo):
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
Thank you very much, Kaj, I really appreciate your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |