Night Shift timings calculation

hi, Anonymous

After my research, This involves the row context and filter context,

You could do these as below:

Step1:

Adjust your formula as below:

TOTAL HRS WORKED = 
VAR WORKINGMIN =
DATEDIFF(MIN(ATTENDANCE[IN TIME]),MAX(ATTENDANCE[OUT TIME]),MINUTE)
RETURN 
VAR WORKINGHRS = 
DIVIDE(WORKINGMIN,60,0)
RETURN
VAR ABC = 
IF(WORKINGHRS>=10,LEFT(WORKINGHRS,3),LEFT(WORKINGHRS,2))+0.01*MOD(WORKINGMIN,60)
RETURN 
VAR P =
MOD(WORKINGMIN,60)
RETURN IF(P=0,WORKINGHRS,ABC)

ABC=IF(WORKINGHRS>=10,LEFT(WORKINGHRS,3),LEFT(WORKINGHRS,2)) +0.01*MOD(WORKINGMIN,60)

do not use "&", this will leads to format error.

Step2:

Use this formula to create a new measure

Measure = var _table=SUMMARIZE(ATTENDANCE,ATTENDANCE[USERID],ATTENDANCE[EMPLOYEENAME],ATTENDANCE[EST DATE].[Date],"aaa",[TOTAL HRS WORKED]) return
CALCULATE(SUMX(_table,[aaa]))

Result:

For example: EMPLOYEENAME is S.Thilak total in 2018-10-19 is 9.57

and in 2018-10-18 is 9.41

now from 2018-10-18 to 2018-10-19 total is 18.98 not 33.11

here is pbix, please try it.

https://www.dropbox.com/s/eitv20zs7bof2ur/Night%20Shift%20timings%20calculation.pbix?dl=0

Best Regards,

Lin

hi, Anonymous

You just add a date column of EST DATE into visual, otherwise, the result will do an aggregation.

for every EST DATE has different time each day, so you need to add a new est date column

new EST DATE = ATTENDANCE[EST DATE].[Date]

the drag the field into the visual

Best Regards,

Lin