Active Minutes during time intervals

[twalsh0625]

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!

[v-juanli-msft]

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

[twalsh0625]

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