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