Amount of cars needed

[Júlio]

Hi everyone, 

I need some help on defining the amount of cars required to attend reservations. 

For instance, I have 2 reservations. One on the period of 23/09/2022 - 27/09/2022, and another on 28/09/2022 - 01/10/2022

So when I filter the period of 23/09/2022 - 01/10/2022, it should show only one car required do attend to this 2 reservations. 

[PaulDBrown]

Can you please provide sample data for the relevant tables

[Júlio]

Hi,

Sure, here it's.

https://1drv.ms/u/s!AhPKKvM6cERc8gIS1mIRoAmFXHbD?e=iMcFBF 

[PaulDBrown]

Thanks for the file. Here is one way, which involves modeling the data slightly differently. This entails changing the structure of the 'Reserves' table to expand the 'Reserves'[Start] to 'Reserves'[End] dates, so you get one row per date for each reservation. With this structure, the calculations are simple:

1) Create the new Reserves Table using:

New reserves =
VAR _Expanded =
    GENERATE ( Reserves, CALENDAR ( Reserves[Start], Reserves[End] ) )
RETURN
    SELECTCOLUMNS (
        _Expanded,
        "_Cod", Reserves[Cod],
        "_Group", Reserves[Group],
        "_status", Reserves[Status],
        "Dates Booked", [Date]
    )

Set up the model as follows (note the inactive relationship between Calendar and Cars):

Now use these measure for the visuals:

Number of Cars = COUNT(Cars[License plate])

Number of Reservations = DISTINCTCOUNT('New reserves'[_Cod])

Cars Avalailable = [Number of Cars] - [Number of Reservations]

The benefit of doing it this way it is that the way you data is more straightforward

New file attached

[Júlio]

This way is really better for the analysis. Thank you.

But the main problem is still happening.

For instance this 2 reserves.

When i filter the date range 24/09/22 - 01/10/2022, should only count as 1 car reserved.

Because the 2 reserves will be attended with the same car.

[PaulDBrown]

The problem is that the reservations table does not include the number plate, so there is no way of knowing from this table which "individual" cars are in use! All we know if there is a reservation for a group in given dates; not which cars are being allotted to each reservation. Make sense?

[Júlio]

What if i count on your new table, the amount for each date (Dates Booked), on the filtered period, and get the max value of that count.

I thik it would work.

Can you help me do it?

[PaulDBrown · Accepted solution]

How about something like this:

New file attached

[Júlio]

I did like this:

It seems to be working fine.

Thanks, you helped me alot.

[Jihwan_Kim]

Hi,

I am not sure how your expected outcome looks like, but I tried to create a sample like below.

It is for creating a new column.

Please check the below picture and the attached pbix file whether it suits your requirement.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Count CC =
VAR _prevenddate =
    MAXX (
        FILTER (
            Data,
            Data[Name] = EARLIER ( Data[Name] )
                && Data[End] < EARLIER ( Data[Start] )
        ),
        Data[End]
    )
RETURN
    SWITCH (
        TRUE (),
        _prevenddate = BLANK (), 1,
        DATEDIFF ( _prevenddate, Data[Start], DAY ) = 1, 0,
        DATEDIFF ( _prevenddate, Data[Start], DAY ) > 1, 1
    )

[Júlio]

Hi,

Thanks for your help,

i have a list of reservations, and the analysis go to cars in the same group.

Let's assume that i receive a new reserve on the period of 24/09/22 - 01/10/22 for the Car Group A, and i only have 2 cars avaliable. I can accept, because the same car will be rented to the reserves 14 and 19, remaining 1 available.

So when i count the number of cars reserved, should be one to this date range.

But only for cars in the same group.

The way i'm doing isn't fiting the two dates, so is counting 2 cars.

Amount Reserves =

CALCULATE( COUNT(Reserves[Car Group]),

    FILTER( VALUES( 'Reserves'[Start]), 'Reserves'[Start] <= MAX(Calendar[Date])),

    FILTER( VALUES( 'Reserves'[End]), OR('Reserves'[End] >= MIN(Calendar[Date]),ISBLANK((Reserves[End])))))