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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.