Forum Discussion

jpurcell's avatar
jpurcell
Advocate I
8 years ago
Solved

Calculated Column - time between events in different rows

I am trying to calculate the time since the last similar event per user. The data set has UserIds, Event types, and a date/time. In events there are open, save, and various other event types. I'm loo...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi jpurcell

     

    These Calculated Columns will also give you desired results

     

    First an Index Column

     

    Index =
IF (
    OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" ),
    RANKX (
        FILTER (
            ALL ( TableName ),
            OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" )
                && TableName[UserID] = EARLIER ( TableName[UserID] )
        ),
        TableName[DateTime],
        ,
        asc
    )
)

    Now the TimeSinceLastSave

     

    Time since last save =
VAR Previoustime =
    CALCULATE (
        VALUES ( TableName[DateTime] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[UserID] ),
            TableName[Index]
                = EARLIER ( TableName[Index] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( TableName[Index] ) )
            && TableName[EventType] = "Save",
        DATEDIFF ( Previoustime, TableName[DateTime], MINUTE )
    )

sandippanchal's avatar
sandippanchal
New Member
7 years ago

Good afternoon All,

 

I am quite new to Power BI and DAX function. I need some help in order to resolve bit of tricky calculation. We have Nurse Call System in place where it collects the data from various Health Care Unit (Rooms). The trigger point and Trigger location panel are dotted around the building including key locations such as rooms, lounges, doors, toilets and assisted bathrooms. Normally, there is two events. Call (Start of Event) or Assistance (Start of Event) to trigger notification to all health care assistant and nurses that Patient in care require assistance. Depending on situation and location, health care assistant or nurses present in building either reset alarm trigger or press Present or Accepted (For allocation and go to physically in room before Resetting the call.). So, in a way, it is multiple start point and multiple end points. In this way, the Start of the event is always Call but end of event could be Reset, Accepted, Present.

 

Below is the sample of data

02/01/2018 09:58

Bedroom 46 U/G

Assistance     

02/01/2018 09:37

Bedroom 4 L/G

Reset          

02/01/2018 09:37

Bedroom 4 L/G

Call           

02/01/2018 09:04

Bedroom 15 L/G

Reset          

02/01/2018 08:52

Bed 69 Poppy

Present        

02/01/2018 08:52

Bed 69 Poppy

Assistance     

02/01/2018 08:42

Bedroom 3 L/G

Reset          

02/01/2018 08:42

Bedroom 3 L/G

Present        

02/01/2018 08:41

Bedroom 3 L/G

Assistance     

02/01/2018 08:39

Bedroom 5 L/G

Reset          

02/01/2018 08:38

Bedroom 5 L/G

Call           

02/01/2018 08:37

Bedroom 16 L/G

Reset          

02/01/2018 08:35

Bedroom 5 L/G

Reset          

02/01/2018 08:35

Bedroom 5 L/G

Assistance     

02/01/2018 08:00

WC Poppy

Present        

02/01/2018 07:58

WC Poppy

Accept         

02/01/2018 07:58

WC Poppy

Call           

02/01/2018 07:56

WC Poppy

Accept         

02/01/2018 07:55

WC Poppy

Call           

02/01/2018 07:28

Bedroom 28 L/G

Present        

02/01/2018 07:27

Bedroom 28 L/G

Accept         

02/01/2018 04:29

Bedroom 16 L/G

Assistance     

02/01/2018 04:27

Bedroom 16 L/G

Accept         

02/01/2018 04:27

Bedroom 16 L/G

Assistance     

02/01/2018 03:41

Bedroom 36 U/G

Present        

02/01/2018 03:40

Bedroom 36 U/G

Accept         

02/01/2018 03:40

Bedroom 36 U/G

Assistance     

02/01/2018 01:26

Bedroom 40 U/G

Present        

02/01/2018 01:26

Bedroom 40 U/G

Reset          

02/01/2018 01:25

Bedroom 40 U/G

Accept         

02/01/2018 01:24

Bedroom 40 U/G

Call           

02/01/2018 00:53

Bedroom 43 U/G

Present        

02/01/2018 00:10

Bed 69 Poppy

Present        

02/01/2018 00:10

Bed 69 Poppy

Assistance     

02/01/2018 00:10

Bedroom 36 U/G

Present        

02/01/2018 00:09

Bedroom 36 U/G

Accept         

02/01/2018 00:09

Bedroom 36 U/G

Call           

01/01/2018 23:49

Bedroom 36 U/G

Reset          

01/01/2018 23:49

Bedroom 36 U/G

Present        

01/01/2018 23:48

Bedroom 36 U/G

Accept         

01/01/2018 23:48

Bedroom 36 U/G

Assistance