Forum Discussion

Sab's avatar
Sab
Icon for Helper V rankHelper V
5 years ago
Solved

Calculate Peak Occupancy rate

Hello,   I created a measure to caclulcate the Occupance rate, which I think is OK: Occupancy Rate = DIVIDE ( SUM ( Reservations[Occupancy (m)] ), DATEDIFF ( MIN ( Reservations[From] ), ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Sab 

    I find the reason that your measure will return 0 when you drill down to day level in your hierachy level.

    Your code:

    Occupancy Rate = 
DIVIDE (
    SUM ( Reservations[Occupancy (m)] ),
    DATEDIFF ( MIN ( Reservations[From] ), MAX ( Reservations[Until] ), DAY ) * 1440,
    0
)

    We see you use DATEDIFF(Min(From),MAX(Until),DAY), when you drill down to the day level, you will calculate the day difference between Min(From) and Max(Until) in the same day.

    For example, Date = 2021/10/22, min from and min until are both in the same day as below.

    Min(From) = 2021/10/22 07:00:00 AM 

    Max(Until) =  2021/10/22 07:00:00 PM 

    DATEDIFF based on day will only return 0, instead of 0.5. This function will only return the whole number day difference.

    So your measure is SUM ( Reservations[Occupancy (m)] )/ 0. Your result is 0.

    If you want to get min difference, using minute in datediff function instead of day *1440.

    Occupancy Rate = 
DIVIDE (
    SUM ( Reservations[Occupancy (m)] ),
    DATEDIFF ( MIN ( Reservations[From] ), MAX ( Reservations[Until] ), MINUTE ),
    0
)

    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.

Sab's avatar
Sab
Icon for Helper V rankHelper V
5 years ago

Thank you amitchandak , almost there.

 

If I use your first (or second) measure, I get this value

 

 

When I use date.Month in your measure instead of just date from the date table, I get the 18.42% which is correct in Monthy level.

 

I think I got to fix my original measure for the occupancy rate (which doesn't work/returns all 0 in days level). Any idea how to calculcate it?

 

So this is my measure for occupancy rate:

 

Occupancy Rate = 
DIVIDE (
    SUM ( Reservations[Occupancy (m)] ),
    DATEDIFF ( MIN ( Reservations[From] ), MAX ( Reservations[Until] ), DAY ) * 1440,
    0
)

So it divides the total occupancy in minutes by the difference of 'min from date' and 'max until date' multiplied by 1440 to convert the value to minutes (which i suspect is wrong!). 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Sab 

I find the reason that your measure will return 0 when you drill down to day level in your hierachy level.

Your code:

Occupancy Rate = 
DIVIDE (
    SUM ( Reservations[Occupancy (m)] ),
    DATEDIFF ( MIN ( Reservations[From] ), MAX ( Reservations[Until] ), DAY ) * 1440,
    0
)

We see you use DATEDIFF(Min(From),MAX(Until),DAY), when you drill down to the day level, you will calculate the day difference between Min(From) and Max(Until) in the same day.

For example, Date = 2021/10/22, min from and min until are both in the same day as below.

Min(From) = 2021/10/22 07:00:00 AM 

Max(Until) =  2021/10/22 07:00:00 PM 

DATEDIFF based on day will only return 0, instead of 0.5. This function will only return the whole number day difference.

So your measure is SUM ( Reservations[Occupancy (m)] )/ 0. Your result is 0.

If you want to get min difference, using minute in datediff function instead of day *1440.

Occupancy Rate = 
DIVIDE (
    SUM ( Reservations[Occupancy (m)] ),
    DATEDIFF ( MIN ( Reservations[From] ), MAX ( Reservations[Until] ), MINUTE ),
    0
)

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.