Count Customers depending on bookings per day

[H3nning]

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!

[H3nning]

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]

@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