Sum of Monthly working time

[Anonymous]

Hello all,

I want to say I have tried some guides for the following topic/problem but I could not get it working.

I am very thankful in advance for your help.

I renamed the example to "Me" but otherwise it is a real example of what data I got.

So basically what I need to show is the first login, last logout, working time per day, working time per month.

While the first 3 are no problem /// filtered by Name and Date

First Login = MIN([Time])

Last Logout = MAX([Time])

working time per day= MAX([Time])-MIN([Time]) 

The problem comes when I try to make a total per month, as MAX([Time])-MIN([Time]) will just find the biggest and smallest value in the whole month and calculate those.

I need to count the total which adds together the daily working times.

May I ask how to make that possible ?

Edit: Sorry for not mentioning that, do not mind the exact working time, minutes, seconds (as this will be over 24hours per month) , I am ok with a calculation by decimal numbers showing only hours.

Date	Name	Time	State
4/14/2020	Me	09:13:38	Login
4/14/2020	Me	10:05:03	Logout
4/14/2020	Me	10:11:18	Login
4/14/2020	Me	11:02:33	Logout
4/14/2020	Me	11:13:56	Login
4/14/2020	Me	11:30:17	Logout
4/14/2020	Me	11:51:29	Login
4/14/2020	Me	14:15:24	Logout
4/14/2020	Me	14:48:29	Login
4/14/2020	Me	15:58:54	Logout
4/14/2020	Me	17:16:12	Login
4/14/2020	Me	17:16:39	Logout
4/15/2020	Me	09:37:25	Login
4/15/2020	Me	14:14:02	Logout
4/15/2020	Me	15:11:00	Login
4/15/2020	Me	17:32:49	Logout

Thank you again

Lubos

[amitchandak · Accepted solution]

First, create a datetime

Datetime = [Date]+[Time]

The use that in min max to get time
sumx(summarize(Table,table[Date],"_1",datediff(min[Datetime],max([Datetime]),hour)),[_1])

You can use AverageX

Summarize at date level to push the row context

Refer: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[Anonymous]

Worked like a charm , thank you @amitchandak