Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpurcell
Regular Visitor

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 looking to find the time at each save instance since the last save or open by that user.

 

the data looks roughly like this:2017-12-07 10_48_45-Book1 - Excel.png

And I'm looking for this:2017-12-07 10_52_57-Book1 - Excel.png

 

 

My assumption is that I would need to make a calculated column that pulls the last date before the date of that row based on filtering for userID and event type. and then you could do a Date Diff. But, any help would be great.

 

 

 

1 ACCEPTED SOLUTION

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 )
    )

1015.png


Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
sandippanchal
New Member

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     

Phil_Seamark
Employee
Employee

Hi @jpurcell

 

I think this calculated table might be close

 

New Table = 


VAR SelfJoin =  
    FILTER( 
        CROSSJOIN( 
                    'Events', 
                    SELECTCOLUMNS( 
                        FILTER('Events', [EventType] in {"Save","Open"}), 
                        "xUserID",[UserID], 
                        "xDateTime",[DateTime] 
                    ) 
                ), 

                [xDateTime] < [DateTime] && 
                [UserID] = [xUserID] 
               
                ) 

VAR LastPurchases =  
    GROUPBY( 
            SelfJoin, 
            'Events'[UserID], 
            Events[DateTime], 
            "Last DateTime",MAXX( 
                                CURRENTGROUP(), 
                                [xDateTime] 
                                ) 
                        
            ) 

RETURN 
    
     ADDCOLUMNS( 
         NATURALLEFTOUTERJOIN('Events',  LastPurchases), 
         "Days since last purchase" ,  IF('Events'[EventType]="Save" , DATEDIFF([Last DateTime],[DateTime],MINUTE),BLANK())
         )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the help! Both solutions worked perfectly. I loved the simplicity of the table, but I'm going to use the columns so I don't have duplicate data in my report. 

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 )
    )

1015.png


Regards
Zubair

Please try my custom visuals

Hello there,

            I have a similar issue where I have several colmns but I just need time difference from the same colmn/EventDt time based of different colmn that has OrderStatus. Kindly help!! I am really new to power BI, dont use DAX a lot.My table looks like below.

Order ID/ EventDt Time/ OrderStatus. each is a colmn.

for a single order at different timings the status changed from recieved to filled to packaged to shipped. I need the average and also the time difference. Please feel free to ask any questions for clarification.

      

Hi all

I tried this formula on my situation because it looks like the same goal, but when adapting the formula it only works partly. The strange thing is that once in a while it actually calculates the time between the events. (in the table shown below It only calculated the last event) .

 

My situation is show below. I want to calculate the time between when an event occurs ‘’Gekomen’’ and when the events disappears ‘’Gegaan’’, shown in the Status row. The Events have an ID which is connected by an type of event, so it can occur multiple times, I want to know the time between the status (gegaan & gekomen). (the difficult part is that all the values are appearing trough each other and  the events can appear and disappear at the same time, also the events can happen multiple times in a short time.

 I hope you can help me!

 

Status                   Date and Time                   ID           Index    Duration

Gegaan                1-2-2021 09:34                  10           7            

Gegaan                1-2-2021 09:34                  637         7            

Gegaan                1-2-2021 09:34                  641         3            

Gegaan                1-2-2021 09:34                  641         3            

Gegaan                1-2-2021 09:35                  637         9            

Gegaan                1-2-2021 09:35                  10           9            

Gegaan                1-2-2021 09:37                  635         1            

Gegaan                1-2-2021 09:37                  626         3            

Gekomen             1-2-2021 09:37                  635         1            

Gekomen             1-2-2021 09:37                  635         1            

Gegaan                 1-2-2021 09:37                  635         1            

Gegaan                 1-2-2021 09:37                  626         3            

Gekomen              1-2-2021 09:37                  655         1            

Gegaan                 1-2-2021 09:37                  655         1            

Gekomen              1-2-2021 09:38                  656         1            

Gegaan                 1-2-2021 09:39                  656         2             1

Can I have this results only using measures?

 

 

That was quite ingenious .....Champ @Phil_Seamark   Smiley Happy


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.