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] ), MAX ( Reservations[Until] ), DAY ) * 1440,
    0
)
 
And I am able to create a chart like below, but oor some reason this measure doesn't work when I drill down to Days!:
 
Now I need to know what is the Peak occupancy rate (single % value) and the Day in which that peak occupancy happened.
1. How to write that measure?
2. Should I change the existing measure to something else, in order to see the occupancy rate by days?
Thank you
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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Sab , You can use top N for that

    for max value

    calculate(maxx(Values(Date[Date]), [Occupancy Rate]), allselected(Date[Date]))

     

    or

    CALCULATE( [Occupancy Rate],TOPN( 1,allselected(Date[Date]),[Occupancy Rate],DESC), values(Date[Date]))

     

    date of max

     

    CALCULATE( max(Date[Date]),TOPN( 1,allselected(Date[Date]),[Occupancy Rate],DESC), values(Date[Date]))

     

    Draw the first measure a second line with marker

     

    • 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.