cancel
Showing results 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.

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

 Customer Booking Type Start End 1 A 01.01.2020 01.01.2022 1 A 03.01.2022 03.01.2022 1 B 03.012022 05.01.2022 2 B 01.01.2022 01.01.2022 2 B 01.01.2022 01.01.2022 2 B 03.01.2022 03.01.2022

Result should be

 Day A-Type Customers B-Type Customers AB-Type Customers 01.01.2022 1 1 0 02.01.2022 0 0 0 03.01.2022 0 1 1 04.01.2022 0 1 0 05.01.2022 0 1 0

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

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

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors