Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'd like the average date/time for events that fall within 2 hours of eachother, when the identification number and calendar dates are the same. For example if an event occured on 25/11 at 4.12pm and 25/11 4.07pm for the same Identification number, I'd like the average column to display 25/11 4.09pm.
I'd like to create the following three columns:
(1) Average time column under the following conditions:
(2) Flagging times that are more than 2 hours apart, but less than 4 hours apart (as 'yes' or blank when not applicable)
(3) Flagging times that are more than 2 hours apart, but less than 8 hours apart (and are not between 2 and 4 hours apart) (as 'yes' or blank when not applicable)
Please see the layout I'd like below, with notes for what I'm looking for in each column:
Identification number | Date/Time of event | Average date/time of event | Time elapsed >2<4 hours | Time elapsed >2<8 hours |
1 | 25/11/2019 16:12:13 | |||
1 | 25/11/2019 16:07:35 | i.e. average of this date/time and previous cell | ||
1 | 21/11/2019 08:25:09 | |||
1 | 21/11/2019 08:23:52 | i.e. average of this date/time and previous cell | ||
1 | 20/11/2019 18:23:31 | |||
1 | 20/11/2019 18:20:57 | i.e. average of this date/time and previous cell | ||
2 | 18/11/2019 18:42:17 | |||
2 | 18/11/2019 18:38:04 | i.e. average of this date/time and previous cell | ||
2 | 18/11/2019 08:27:13 | |||
2 | 18/11/2019 08:26:19 | |||
2 | 18/11/2019 08:25:46 | i.e. average of this date/time and 2 previous cells | ||
2 | 08/11/2019 07:54:16 | Remains the same, as no other time within 2 hours to average with | ||
3 | 10/12/2018 17:38:17 | Not averaged, as next time is greater than 2 hours | Flagged here ("Yes") | |
3 | 10/12/2018 21:21:25 | Not averaged, as earlier time is greater than 2 hours | Flagged here ("Yes") |
Many thanks in advance!
Good advice to you: Do not calculate this in DAX. Use Power Query... unless you want to suffer. Trust me, this is very good advice which you should follow.
Thanks so much for your response and for your help @amitchandak. Unfortunately the average doesn't work, as it needs to take into account the number of events in that period of being within 2 hours of eachother - which isn't always 2 events.
I'm wondering if there's a way to group together times within 2 hours of eachother and create an average of the group?
I'm new to Power BI, but was able to find previous forums to determine time elapsed:
@Anonymous , Try a new column like
new column =
var _1 = maxx(filter(Table1, Table1[Datetime] < earlier(Table1[Datetime]) && Table1[ID] =earlier(Table1[ID]) ), [Datetime])
var _2 = datediff(_1,[Datetime],hour)
return
if(_2<2, [Datetime]+ ([Datetime] - _1)/2, blank() )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |