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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
H3nning
Resolver I
Resolver I

Count Customers depending on bookings per day

Hi, i need help with a tough calculation i have to do.

 

I have customers and these customers have bookings lasting several days or even month. So each booking has a start and an end. And i have two different types of bookings, lets say A and B.

 

I want to calculate for each day how many customers I have with only A bookings, with only B bookings and with A and B bookings.

 

Bookingdata could look like this

 

CustomerBooking TypeStartEnd
1A01.01.202001.01.2022
1A03.01.202203.01.2022
1B03.01202205.01.2022
2B01.01.202201.01.2022
2B01.01.202201.01.2022
2B03.01.202203.01.2022

 

Result should be

DayA-Type CustomersB-Type CustomersAB-Type Customers
01.01.2022110
02.01.2022000
03.01.2022011
04.01.2022010
05.01.2022010

 

I also have a customers table and a date table as dimensions.

 

I managed to measure customers of a and b type seperately without taking into account if they have both with for example for A:

Calculate(Distinctcount(Customer),Filter(all(Bookingdata), Start<= Day && End >= Day && Booking Type = A))

 

But that way I cannot count only those with both A and B and these are counted double if I count A and B seperately.

 

Hope someone can help out. Thanks!

2 REPLIES 2
H3nning
Resolver I
Resolver I

Hi @amitchandak , thank you but that I already manged even with connected table. My Problem is that every ID has multiple entries with two diofferent states. And I want to count per day: How many with state A, how many with state B and most importantly how many with A and B without counting them double!

amitchandak
Super User
Super User

@H3nning , Create a disconnected date table, means do not join with your table

Then create a measure and use along with date from date table in visual

 

Calculate(distinctcount(Bookingdata[Customer]), filter(Bookingdata, Bookingdata[Start] >= Max(Date[Date]) && Bookingdata[END] >= Max(Date[Date]) ))

 

Check the HR example file attached

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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