Forum Discussion

twalsh0625's avatar
twalsh0625
Icon for Advocate I rankAdvocate I
6 years ago

Active Minutes during time intervals

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.

 

EmployeeDuration(Secs)Start TimeEnd TimeState
Arnold3005/4/2020 9:34:32 AM5/4/2020 9:39:32 AMActive
Arnold36005/4/2020 9:39:32 AM5/4/2020 10:39:32 AMLunch
Arnold18105/4/2020 10:39:32 AM5/4/2020 11:09:42 AMResearch

 

And so on.... we would like to display as (if filtered only to Arnold):

 

Interval (Start)Active SumLunch SumResearch Sum
9:00300  
9:30 1228 
10:00 1800 
10:30 5721228
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!

Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      twalsh0625
      Icon for Advocate I rankAdvocate I
      6 years ago

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