Forum Discussion
Occupancy Calculation based on Date Time
Hello,
Was trying to come up with the occupancy of a room.
This is calcuated based on the number of people entering[arrival date/time] and leaving the room[ED Disposition Date/Time] using date time
Expected Outcome
| Time and Date | Number of Outcomes |
| 24/9/2022 0900 | 2 |
| 24/9/2022 1000 | 3 |
Hi zacharyyyy ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = GENERATESERIES(MIN('Table'[Arrival Date/time]),MAX('Table'[ED Disposition Date/Time]),TIME(1,0,0))Table 3 = CROSSJOIN('Table','Table 2')Table 4 = FILTER('Table 3', 'Table 3'[Value]>='Table 3'[Arrival Date/time]&&'Table 3'[Value]<='Table 3'[ED Disposition Date/Time])2. Put the [Value] and [Arrival Date/time] of Table4 into the visual, and set [Arrival Date/time] to Count.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Adding data in a copy/paste-able format would help get a specific solution, but please see this article in video. The techniques described would be a way to solve this one.
Calculate and Format Durations in DAX – Hoosier BI
(2) Simple Model, Simple DAX - Line Time Example - YouTube
Pat
zacharyyyy can you provide data that someone wouldn't have to retype?
Hi zacharyyyy ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = GENERATESERIES(MIN('Table'[Arrival Date/time]),MAX('Table'[ED Disposition Date/Time]),TIME(1,0,0))Table 3 = CROSSJOIN('Table','Table 2')Table 4 = FILTER('Table 3', 'Table 3'[Value]>='Table 3'[Arrival Date/time]&&'Table 3'[Value]<='Table 3'[ED Disposition Date/Time])2. Put the [Value] and [Arrival Date/time] of Table4 into the visual, and set [Arrival Date/time] to Count.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
