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 tim...
Need Help
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] )
    )
)