Forum Discussion
Night Shift timings calculation
Hello Folks,
I am calculating shift timing of night employees .
For night shift, I am calculating Minimum EST In time and while showing i am using IST time.
So the problem is If I look at one day data it gives perfect working hours , but when I increase date range then it sum up working hours.
I am attaching Snap for calrification :
Below snap is for one day :
Below snap is for multiple dates :
Thanks In Advance !!
Regards,
Vivek
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
6 Replies
hi, Anonymous
Could you please share your sample pbix file or some data sample with the measure formual of total hours in and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi v-lili6-msft ,
Thanks for reply ,
please find below link of OneDrive where I uploaded my sample pbix file:
Regards,
Vivek
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 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
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