Help with automatic assignation of bookings to a room

[wtdpon]

Hi everyone, we are facing a problem in power bi that we cannot figure out. We are either missing some logic or this can't be done automatically...

The Dataset

We have a very simple dataset with just this information: supplier, booking id, start date, end date. I am attaching a file with sample data with just one supplier that is enough to represent the issue: https://we.tl/t-abtKfFF7Yu

The Problem

When a booking comes in, we want to do an automatic assignation of the booking to a room. For the specific supplier in the sample data, we hace 7 available rooms. So, when a booking comes in, we want to check if there are any active bookings prior, and assign it to a free room.

We have created the following columns:
Order: it's just a rank of the booking id by start date for each supplier [Probably need to add something so the rank does not get repeated when two bookings start at the same time]

RANKX (
FILTER(data, [Supplier] = EARLIER([Supplier])),
[Start],
,
ASC,
Dense
)

Occupation: it's the rooms that are occupied when the booking starts (it includes the current booking we are looking at)

Occupation =
CALCULATE(
DISTINCTCOUNT(data[Booking id]),
ALLEXCEPT(data,data[start])

Active Bookings at Start: Similar to occupation, but only using rows above the current one. So basically, how many active bookings we have when the current one starts.

Active when start =
var rank_ = data[Order]
var start_date = data[Start]
var end_date = data[End]
return
CALCULATE(DISTINCTCOUNT(data[Booking ID]),
FILTER(data,
data[Order] < rank_ &&
data[Start] <= start_date &&
start_date <= 'data[End]
))

The issue is that we don't know how we can have into account the bookings that have finished (and how to calculate their room number) because the assignation should always be from min to max room number.

So, for example:

Just editing to add that I'm looking at this post by @Greg_Deckler about 'previous value' and recursion: For and While Loops in DAX - Microsoft Power BI Community
But I'm not sure if this would help and would like to avoid it

[wtdpon]

Hi, 
in the end we have done this in python. 
If anyone can think of a way of managing it in DAX, it wil be very appreciated. Thx!

[v-rzhou-msft]

Hi @wtdpon ,

I am so glad that you could solve your issue by python. According to your statement, I think your requirement is looping. However, Dax doesn't support us to calculate with looping logic in Power BI. I suggest you to try Power Query.

For reference:

List.Generate() and Looping in PowerQuery

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.