Calculate Peak Occupancy rate

[Sab]

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

[Sab]

Thanks Rico, well explained!

[amitchandak]

@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]

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!). 

[v-rzhou-msft · Accepted solution]

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.

[v-rzhou-msft]

Hi @Sab 

I think you use Auto date/time intelligence in your report, try to build a calendar table and add Year/Month/Quarter column in it instead of using Auto date/time intelligence. Could you share a sample with me by your Onedrive for Business? This may make it easier for me to understand your data model and requirement.

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.