Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
H3nning
Resolver I
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 IDCustomerStartEnd
1A1.1.202031.10.2020
2A1.12.202031.03.2021
3A1.1.202031.10.2020
4A1.1.202101.11.2021
5B1.1.202031.03.2020
6B01.09.202131.12.2021

 

Date:

A normal Calendar table with every day as one line

 

Customer:

Customer IDCustomer Data
AName1
BName2

 

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:

MonthDistinctcount Customers longer 1 year
Jan 200
Feb 200
Mar 200
...0
Dez 200
Jan 211 (Customer A)
Feb 211 (Customer A)
Mar 211 (Customer A)
Apr 211 (Customer A)
May211 (Customer A)
Jun 211 (Customer A)
Jul 211 (Customer A)
Aug 211 (Customer A)
Sep 212 (Customer A and B, B is active now an over all older than 1 year)
Oct 212 (Customer A and B, B is active now an over all older than 1 year)
Nov 212 (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 211 (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
v-yilong-msft
Community Support
Community Support

Hi @H3nning ,

Based on your problems, here are my answers.

First I create three tables as you mentioned.

vyilongmsft_0-1708581842180.png

vyilongmsft_1-1708581883839.png

vyilongmsft_2-1708581915219.png

Then I write a  measure.

Formatted Date = FORMAT('Date'[Date], "d.M.yyyy")

vyilongmsft_3-1708582190131.png

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

vyilongmsft_4-1708582341410.png

 

 

 

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.

 

View solution in original post

3 REPLIES 3
H3nning
Resolver I
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!

v-yilong-msft
Community Support
Community Support

Hi @H3nning ,

Based on your problems, here are my answers.

First I create three tables as you mentioned.

vyilongmsft_0-1708581842180.png

vyilongmsft_1-1708581883839.png

vyilongmsft_2-1708581915219.png

Then I write a  measure.

Formatted Date = FORMAT('Date'[Date], "d.M.yyyy")

vyilongmsft_3-1708582190131.png

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

vyilongmsft_4-1708582341410.png

 

 

 

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.