calculating working hours per day for each employee

[Hwahab]

Hi , Need to calcuate working hours for each employee per day .

i done split the date time column to 2 columns ( date and Time )

The dataSet is look like the following

[v-danhe-msft · Accepted solution]

Hi, based on my test , you can refer to below steps”

1.I have entered some sample data to test for your problem like the picture below.

2.Create a new measure to calculate your working hours.

Measure = var a=CALCULATE(MAX(Sheet1[Date Time]),FILTER(ALL(Sheet1),Sheet1[Action]="In"&&FORMAT(Sheet1[Date Time],"dd/mm/yyyy")=FORMAT(MAX(Sheet1[Date Time]),"dd/mm/yyyy")&&Sheet1[User ID]=MAX(Sheet1[User ID])))

          var b=CALCULATE(MAX(Sheet1[Date Time]),FILTER(ALL(Sheet1),Sheet1[Action]="Out"&&FORMAT(Sheet1[Date Time],"dd/mm/yyyy")=FORMAT(MAX(Sheet1[Date Time]),"dd/mm/yyyy")&&Sheet1[User ID]=MAX(Sheet1[User ID])))

          return DATEDIFF(a,b,MINUTE)/60

3.Create a Table visual and add the related fields and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ehia0o2j5hnjp0s/calculating%20working%20hours%20per%20day%20for%20each%20e...

Regards,

Daniel He

[Hwahab]

Hi Daniel , Thank you so much . i just found somthing related to logic and would like to share with you

as in this table the DAX cacluate working hours in negative (-) due to employees that work 12 hours direct ,

is there way to consdier this in the DAX ?

Status	User ID	Name	Clock Time	Att. Type	Type
New	1	Michael	6/10/2018 21:31	in	PM
New	1	Michael	6/11/2018 9:11	out	AM
New	1	Michael	6/11/2018 21:24	in	PM

[Hwahab]

Thank you so much .