Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
twalsh0625
Advocate I
Advocate I

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!

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.