cancel
Showing results for
Did you mean:
Helper V

## 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
1 ACCEPTED SOLUTION
Community Support

Hi @Sab

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

``````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 5
Helper V

Thanks Rico, well explained!

Super User

@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

Helper V

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

Community Support

Hi @Sab

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

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

Community Support

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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors