Forum Discussion

Georgetimes's avatar
Georgetimes
Frequent Visitor
2 years ago
Solved

Inventory data help - calculate usage

Hi everyone,

 

Have the below sample data in two different tables.

 

BookingIdPickup DateDropOff DateBicycle Type
127/02/202410/03/2024A
227/02/202428/02/2024A
328/02/202429/02/2024A
    

 

Date_of_reportBicycle TypeNumber of bicycles in stock
27/02/2024A2
28/02/2024A2
   

 

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

 

DateBookingsUsage
27/02/20242100%
28/02/20242100%
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.