Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
Job has me back in Power BI after a long stint in Tableau and I'm up against a problem thats common in our company.
We have tables showing employee activity with a start and end time stamp and activity type, Below is a sample.
We would like to sum the total minutes/hours/seconds (units of time will be depenedent on user preference) that each employee spent in each state in each interval of time.
Employee | Duration(Secs) | Start Time | End Time | State |
Arnold | 300 | 5/4/2020 9:34:32 AM | 5/4/2020 9:39:32 AM | Active |
Arnold | 3600 | 5/4/2020 9:39:32 AM | 5/4/2020 10:39:32 AM | Lunch |
Arnold | 1810 | 5/4/2020 10:39:32 AM | 5/4/2020 11:09:42 AM | Research |
And so on.... we would like to display as (if filtered only to Arnold):
Interval (Start) | Active Sum | Lunch Sum | Research Sum |
9:00 | 300 | ||
9:30 | 1228 | ||
10:00 | 1800 | ||
10:30 | 572 | 1228 | |
11:00 | 582 |
With all the appropriate relationships for things like date and employee in tact.
I tried a few solutions found here but haven't gotten any to work. Any experience or insight is welcome.
thanks!
Hi @twalsh0625
Could you tell me the rule to divide "Durations" for diffrent Intervals?
For example, how to divide Duration 3600 to 1228, 1800, 572?
Best Regards
Maggie
Hi, Thanks for the response. I acutally ended up solving this with a few glasses of wine and screams of rage:
Time_in_State = CALCULATE (
SUMX (
'Emp_Activity_Details',
DATEDIFF (
MAX ( MIN('Hours'[Interval_Start]) , 'Emp_Activity_Details'[Start_Time] ),
MIN ( MAX('Hours'[Interval_end]), 'Emp_Activity_Details'[End_Time] ),
SECOND
)
),
FILTER (
'Emp_Activity_Details',
'Emp_Activity_Details'[Start_Time] <= MAX(Hours[Interval_end])
&& 'Emp_Activity_Details'[End_Time] >= MIN( Hours[Interval_Start] )
)
)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |