Forum Discussion
Distinct count depending on multiple lines
Hi,
i have a problem counting the number of customers that are customers for a certain period of time. Important: I dont want to count them once today, but for any given moment in time!
I have the following tables:
Contract:
| Contract ID | Customer | Start | End |
| 1 | A | 1.1.2020 | 31.10.2020 |
| 2 | A | 1.12.2020 | 31.03.2021 |
| 3 | A | 1.1.2020 | 31.10.2020 |
| 4 | A | 1.1.2021 | 01.11.2021 |
| 5 | B | 1.1.2020 | 31.03.2020 |
| 6 | B | 01.09.2021 | 31.12.2021 |
Date:
A normal Calendar table with every day as one line
Customer:
| Customer ID | Customer Data |
| A | Name1 |
| B | Name2 |
I want to build a diagram with date from Date table on x Axis and Number of distinct customers which were customer longer than 1 year at the given time. It is not important, that the customers covered every day during that period, but he has to have a contract in that moment or on at least one day in the given period.
So the result should be if visualized with granularity month:
| Month | Distinctcount Customers longer 1 year |
| Jan 20 | 0 |
| Feb 20 | 0 |
| Mar 20 | 0 |
| ... | 0 |
| Dez 20 | 0 |
| Jan 21 | 1 (Customer A) |
| Feb 21 | 1 (Customer A) |
| Mar 21 | 1 (Customer A) |
| Apr 21 | 1 (Customer A) |
| May21 | 1 (Customer A) |
| Jun 21 | 1 (Customer A) |
| Jul 21 | 1 (Customer A) |
| Aug 21 | 1 (Customer A) |
| Sep 21 | 2 (Customer A and B, B is active now an over all older than 1 year) |
| Oct 21 | 2 (Customer A and B, B is active now an over all older than 1 year) |
| Nov 21 | 2 (Customer A and B, B is active now an over all older than 1 year, A has at least one day during that period) |
| Dec 21 | 1 (Customer B, A is not active anymore) |
Im very curious if someone can figure that out. It should be a measure, so the granularity is dynamic (year, quarter, month, day). Would be great if someone helps out, because i find it really hard to figure out... Thanks!
Hi H3nning ,
Based on your problems, here are my answers.
First I create three tables as you mentioned.
Then I write a measure.
Formatted Date = FORMAT('Date'[Date], "d.M.yyyy")Finally I write another measure and you will get what you want.
MEASURE = VAR CurrentDate = SELECTEDVALUE ( 'Date'[Formatted Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Contract[Customer] ), FILTER ( 'Contract', Contract[Start] <= CurrentDate && Contract[End] >= CurrentDate ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi H3nning ,
Based on your problems, here are my answers.
First I create three tables as you mentioned.
Then I write a measure.
Formatted Date = FORMAT('Date'[Date], "d.M.yyyy")Finally I write another measure and you will get what you want.
MEASURE = VAR CurrentDate = SELECTEDVALUE ( 'Date'[Formatted Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Contract[Customer] ), FILTER ( 'Contract', Contract[Start] <= CurrentDate && Contract[End] >= CurrentDate ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That only counts the numer of customers but not those older than one year. But i could manage that by writing the lowest Starting date of any contract on each customer:
LowestStart=Min(Start)
And bringing that to the contract:
LowestStartOfCustomer=Related(Customer[LowestStart])
That i can include i a datediff clause in the conditions of your measure. So thank you!
Anonymous do you think you can have a look at this too:
I took your idea and I need to develop it further if type of contract is different. Thank you!
