Distinct count depending on multiple lines

[H3nning]

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!

[H3nning]

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

[v-yilong-msft · Accepted solution]

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.

[H3nning]

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!