Forum Discussion
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
Community Supporthi, 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 v-lili6-msft ,
Thank you Sir.
But can we show two different rows for a employee for two different dates.
From your given 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
So instead of Showing Total 33.11 , i need to show his working hours day wise.
Is there any way to show this.
Thanks Once again !!
Regards,
Vivek
- v-lili6-msft
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
Hi v-lili6-msft Sir .
Thanks a lot , I am struggling to get this result from past few days but you made it look easy.
Thanks & Regards,
Vivek