Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
And I'm looking for this:
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.
Solved! Go to 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 ) )
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 |
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()) )
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 ) )
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |