Please find my solution attached.
Basically, you need join your data to a calendar table as shown below by using the key 1=1
And then create the calculation as below and build the view.
Use calendar as your date filter
Hope this helps
Book2 (15)_v10.2.twbx 14.7 KB
You can do this with just a parameter and calculation. Workbook is attached.
Add a Parameter "WorkDay" of type Date, select show parameter
Add a Calculated Field "WorkOnSelectedDay" (optional but allows you to use parameter to filter:
[WorkDate] = DATE([Site Enter Time]) or [WorkDate] = DATE([Site Exit Time])
Add Calculated Field "WorkHours" : (note: I left your LOD express in place in the twbx, but in my example they weren't needed
if [WorkOnSelectedDay] then
if Date([Site Enter Time]) = Date([Site Exit Time]) then
DATEDIFF('hour',[Site Enter Time],[Site Exit Time])
elseif [WorkDate] = DATE([Site Enter Time]) then
DATEDIFF('hour',[Site Enter Time],DateAdd('day',1, [WorkDate]))
DATEDIFF('hour',[WorkDate],[Site Exit Time])
You can filter on WorkOnSelectedDay = true to use parameter as filter
If this helped you, so other can also be helped, please mark as helpful or answered
WorkHours.twbx 16.5 KB
What does "calendar" table contains?
Request you to share calendar table so that i can understand the logic.
If you look at the workbook I attached, and then right click the datasource and select edit data source, you will find a data source from Book1 and sheet1 as below
basically, the calendar table contains the calendar date as you can see, you can create a similar one but more dates as you need.