Time_Interval.xlsx (20.4 KB)
Training_Details.xlsx (10.0 KB)
Hi Expert,
Need your assistance for DAX formula to get the actual numbers of rooms required in the time period as per time interval excel sheet. (As shown from cell C3 to C7)
I have made a sample plotting to drive the the values from F1 to K5 based on Based on Training details file.
Scenario: I have number of trainings conducted each day for 20 mins mentioned in training details file.
I want to calculate how many rooms are being occupied based on time interval sheet.
Keep in mind i have look at each separate time from in order to see the actual room occupied.
For example:
Training A1, Start Time 00:05 to 00:25
Training A2, Start Time 00:10 to 00:30
Training A3, Start Time 00:15 to 00:35
Now in the above example, below is my booking as per 5 min interval
0:05 - 1
0:10 - 2
0:15 - 3
0:20 - 3
0:25 - 3
Since A1 training will book 1 class from 00:05 to 00:25,
A2 training will book 2 class from 00:10 to 00:30,
A3 training will book 3 class from 00:15 to 00:35,
Bit complicated but i hope i was able to explain.
Thank you for your help and guidance.
Note: my actual data have time interval for 1 year with time interval for 5 days.
Objective of this analysis is, My boss wants to know how many rooms we require for one year for all the trainings planned. Based on that we need to acquire more rooms.