Forum Discussion
Creating Calculated Table with Shift Hours
Hi all,
I have a dataset that looks like the one below. (Below is a sample of my real data):
As you can see, for every unique employee and date combination, we have four sets of times. Shift Start time, Break Start time, Break End time and Shift End time.
I want a seperate calculated table that has the total shift hours for every unique employee - date combination. Shift hours is calculated as Shift End time minus Shift Start time minus 30 minutes.
The desired output for my sample is below:
It doesn't matter if the solution is on Power Query level. As long as I get the desired output I'm happy.
Any help is highly appreciated.
Thanks a lot in advance!
Midhun
Anonymous indeed, there are some misconderation and i have updated the reply. see if it is torwards the target.
5 Replies
Hi Anonymous
try to create a table with:SumTable =ADDCOLUMNS(SUMMARIZE(TableName,TableName[Date],TableName[Emp Number]),"Shift Hours",CALCULATE(MAX(TableName[Time]))-CALCULATE(MIN(TableName[Time]))-1/48)i tried and it worked like this:Hi FreemanZ ,
I am trying the SUMMARIZE() approach but i think you misunderstood the shift hours thing. I think your logic prints the difference between the largest and smallest times in the entire dataset.
My shift hours logic is Shift End minus Shift Start minus 30 mins, where Shift Start and Shift End are times for each employee for that particular day. (see sample data I posted above).
Kind regards,
Midhun
Anonymous indeed, there are some misconderation and i have updated the reply. see if it is torwards the target.