cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sab
Resolver II
Resolver II

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!:
vysoft RMS Reporting - Power BI Desktop.jpg
 
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
RicoZhou
Community Support
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.

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.

View solution in original post

5 REPLIES 5
Sab
Resolver II
Resolver II

Thanks Rico, well explained!

amitchandak
Super User
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

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Thank you @amitchandak , almost there.

 

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

 

vysoft RMS Reporting - Power BI Desktop_2.jpg

 

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

 

RicoZhou
Community Support
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.

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.

RicoZhou
Community Support
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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors