Overtime Calculation

[Lucy64]

I have seen other topics relating to overtime but the calculation I need appears more complex:

I have data with [Date], [Shift], [ItemNumber].  There can be several entries for a shift on any one day for each ItemNumber they work on.  Each shift should work 8hrs per day and any additional hours should be calculated as overtime.  The data is stored in a SQL Database and I am using DirectQuery to connect to the data.

I need to show a graph showing just the overtime for each shift with ability to drill down through date i.e. Year, Quarter, Month, Day.

Any ideas?

[Anonymous]

@Lucy64

Do you have any time brackets, like anything over 5 PM should be cateogrised as overtime? If so, you can use that as a filter in your calculation. Otherwise, you can calculate a time difference of start & end time in a separate column. On the report, you can use one of the filters (visual/page/report) to only show data where difference is more than 8 hours. 

Thanks

Kaz