SPA Occupancy - based on Hours calculation

[Ivo]

Hi

I watched video on “Calculate Occupancy in a hospital”. Where occupancy is based on DAYS.
I used provided formula, but I’m getting a bunch of zeros, not always in right places…

I think it’s not only DAX issue but also how I created & connected Date and Time tables.

NOTE: if a SPA treatment is 25 min long, it counts as if SPA was occupied full 30min.
i.e. always rounded-up to the closest 30min

attaching my .pbix, data file and expected results file
T_SPA_EDNA.pbix (411.1 KB)
SPA_EDNA.csv (17.0 KB)
expected results.xlsx (20.0 KB)

PS/ txs for video on how to protect & keep my data secure.

[EnterpriseDNA]

Welcome to the forum @Ivo

Bumping this post for more visibility.

[JBocher]

Hi @Ivo,

To answer to your problem, I have first created a supporting table “Times” that is not linked to your others tables.

This table is the column header of the matrix you want and I will use it in my measure.
Here is the Occupancy measure.

It is a simple if function to measure if the “Times” is between the start time and the end time.

Finally for your result table, I needed to create a new measure to sum.

Here is the final table.

I hope it will help you

Full solution below
T_SPA_EDNA.pbix (413.1 KB)

Best regards,
JBocher

[Ivo]

Thank you!
I need to get most recent PBI desktop version before I can open your file.
But above looks perfect.
I will manually retype your steps.

I’m going to mark it as “solution provided” - thank you for taking time to help me

[Ivo]

I’m deleting this last comment. I was able to figure it out.