Inventory data help - calculate usage

[Georgetimes]

Hi everyone,

Have the below sample data in two different tables.

BookingId	Pickup Date	DropOff Date	Bicycle Type
1	27/02/2024	10/03/2024	A
2	27/02/2024	28/02/2024	A
3	28/02/2024	29/02/2024	A

Date_of_report	Bicycle Type	Number of bicycles in stock
27/02/2024	A	2
28/02/2024	A	2

Table A: bookings - need to calculate how many bookings per day (based on pick up date)

Table B: stock of bicycles - this is updated daily with number of current stock

For example on 27/02/2024 there were 2 bookings (booking Id 1 and 2) and on 27/02/2024 the stock for bicycle type A was 2, therefore we've used 100% of inventory. (2 bookings for bicycle type A /  divided by stock of A which is 2)

The problem is next day (28/02/2024) - there was only 1 booking (bookingId 3), however bookingId 1 has a drop off date after 28/02/2024, so in total there were actually 2 bookings "divided" stock for bicycle type A - this should result in 100% usage again.

I've tried different ways but for the second day I only get 1 booking in my calculation for bicycle type A

I will need at the end to have multiple visualisations, one of them like below:

There are multiple bicycle types, however I've only provided sample with "A" just to keep it simple, but that needs to be taken in consideration when calculating the bookings as the below table will have a drill down to see booking numbers by Date and Type

Date	Bookings	Usage
27/02/2024	2	100%
28/02/2024	2	100%

[Anonymous · Accepted solution]

Hi @Georgetimes 

For your question, here is the method I provided:

Here's some dummy data

"A"

"B"

Create a measure. Types are grouped, and the calculation rule is that Pickup Date is equal to Date_of_report or DropOff Date is greater than Date_of_report.

Bookings = 
VAR b_DATE = SELECTEDVALUE('B'[Date_of_report])
RETURN 
    CALCULATE(
        COUNTROWS('A'), 
        FILTER(
            ALL('A'), 
            'A'[Bicycle Type] = MAX('A'[Bicycle Type]) 
            && 
            ('A'[Pickup Date] = b_DATE || 'A'[DropOff Date] = b_DATE)
        )
    )

Create a measure to calculate usage.

Usage = DIVIDE([Bookings], SELECTEDVALUE('B'[Number of bicycles in stock]))

Here is the result.

Regards,

Nono Chen

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