Computing a sequence of events over time and extract a percentage of duration

Question

I have a dataset which stores events regarding the availability status of a room.

For example, if someone is entering the room at 8:30 am, I get the following row in my table :

 #   room  status    date 
---  ----  --------  ------------------- 
 0   A1    OCCUPIED  2022-01-01 08:30:00

A similar event is created when this person is leaving the room. My table would then look like this :

 #   room  status     date 
---  ----  ---------  ------------------- 
 0   A1    OCCUPIED   2022-01-01 08:30:00
 1   A1    AVAILABLE  2022-01-01 09:15:00

In practice, the table has way more entries, and data are intertwined.

 #   room  status     date 
---  ----  ---------  ------------------- 
 0   A1    OCCUPIED   2022-01-01 08:30:00 <--
 1   B4    OCCUPIED   2022-01-01 08:32:00
 2   C2    OCCUPIED   2022-01-01 08:41:00
 3   A1    AVAILABLE  2022-01-01 09:15:00 <--
 4   C2    AVAILABLE  2022-01-01 09:20:00
 5   A1    OCCUPIED   2022-01-01 09:30:00 <--
 6   B4    AVAILABLE  2022-01-01 10:00:00
 7   A1    AVAILABLE  2022-01-01 12:00:00 <--

I am currently looking for a way to extract a percentage/duration of availability from each of my rooms, but I don't know how to proceed.

I have created a few measures :

// A measure to count the total of status
Count status = COUNT(myTable[status])

// A calculated measure for available ones
Total available = CALCULATE([count status], myTable[status]=="AVAILABLE")
// A calculated measure for occupied ones
Total occupied = CALCULATE([count status], myTable[status]=="OCCUPIED")

I already have a date hierarchy which means I can change the granularity from year to month, to week day, to hour of the day. I can also apply a filter to select a range of hours, for example 8:00 to 18:00.

The problem is, the measures I have created simply count the number of changes that occur in a given period (in the chart below, the hours), but they don't reflect the actual duration of each event, which means that my graph is actually wrong.

If I take my room A1 as an example, in the actual configuration, my graph would look like this :

            ___ ___ ___ ___ ___ ___ ___ ___
           | 0 |   |   |   |   |   |   |   |      
available  |   | 50|   |   |100|   |   |   |         
           |   |___|   |   |   |   |   |   |     
           |100|   |   |   |   |   |   |   |
occupied   |   | 50|   |   | 0 |   |   |   |
           |___|___|___|___|___|___|___|___|
             8   9  10  11  12  13  14  15

In the column 8, 100% occupied because 1 entry in the dataset for this status vs 0 entry for "available".
In the column 9, 50-50 because 1 entry for each status (one at 09:15, the other at 09:30)
...

The result I am looking for is this one :

            ___ ___ ___ ___ ___ ___ ___ ___
           |   | 25| 0 | 0 |   |   |   |   |      
available  | 50|___|   |   |100|100|100|100|         
           |___|   |   |   |   |   |   |   |     
           |   | 75|100|100|   |   |   |   |
occupied   | 50|   |   |   | 0 | 0 | 0 | 0 |
           |___|___|___|___|___|___|___|___|
             8   9  10  11  12  13  14  15

In the column 8, I would get 50-50 because the room was available between 08:00 and 08:30, but then it was occupied
In the column 9, I would get 75% occupied because the room was only available between 09:15 and 09:30
In the column 10, I would get 100% occupied
...

Is it possible to get it through a DAX measure or do I need to restructure some of my data ?

user_1903793 · Answer

The solution to your problem is to add calculated column to your source table which has the time of next Event in the same room. The Room_No here is your category column.

First, add index by category (by Room)

Event_asc = 
VAR Current_Category = Table[Category]
RETURN
RANKX (
    FILTER (
        Table,
        Table[Category] = Current_Category 
    ),
    Table[DateTime], , ASC, Dense
)

Then add this column:

Event_Next_Time =
VAR Current_Category = Table[Category]
VAR CurIndex = Table[Event_asc]
VAR Result =
    CALCULATE(
        MAX( Table[DateTime] ),
        Table[Category] = Current_Category 
            && Table[Event_asc] = CurIndex + 1,
        REMOVEFILTERS()
    )
RETURN
    Result

Once you have it, just add a third column which calculates the difference between two Datetimes (Event and NextEvent).

Lapse = DATEDIFF( Table[DateTime], Table[TimeOfNextEvent], SECOND )

The rest should be easy for you :-)

Computing a sequence of events over time and extract a percentage of duration

1 Answers