cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

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

1 ACCEPTED SOLUTION
Community Support

Hi @H3nning ,

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 3
Resolver I

@v-yilong-msft  do you think you can have a look at this too:

https://community.fabric.microsoft.com/t5/Desktop/Count-Customers-depending-on-bookings-per-day/m-p/...

I took your idea and I need to develop it further if type of contract is different. Thank you!

Community Support

Hi @H3nning ,

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.

Resolver I

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.