Forum Discussion
Timesheet Comparison
Hi,
I have datasets similar to the below.
Dataset 1 - Timesheet Booking Data
| Date | Hours |
| 01/02/2022 | 11 |
| 02/02/2022 | 42 |
| 03/02/2022 | 53 |
| 04/02/2022 | 32 |
| 05/02/2022 | 14 |
| 06/02/2022 | 66 |
| 07/02/2022 | 55 |
| 08/02/2022 | 77 |
Dataset 2 - Timesheet Weekly Summary
| Week Ending Date | Hours Summary |
| 03/02/2022 | 140 |
| 10/02/2022 | 220 |
Dataset 3 - Date Table
| Date | Week Ending Date |
| 01/02/2022 | 03/02/2022 |
| 02/02/2022 | 03/02/2022 |
| 03/02/2022 | 10/02/2022 |
| 04/02/2022 | 10/02/2022 |
| 05/02/2022 | 10/02/2022 |
| 06/02/2022 | 10/02/2022 |
| 07/02/2022 | 10/02/2022 |
| 08/02/2022 | 10/02/2022 |
I am trying to compare dataset 1 & 2 to make sure the hours booked during the week match the hours summary dataset.
Work weeks run from Friday - Thursday. The date table includes all dates and their corrisponding week ending date.
Dataset 2 - Timesheet Weekly Summary shows a summary of all hours booked during that week corrisponding to a week ending date.
DAX Measured needed
1. SUM of of all hours per working week in dataset 1
Hope this makes sense.
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Week = WEEKDAY('Timesheet Booking Data'[Date])Flag = RELATED('Timesheet Weekly Summary'[Week Ending Date])Flag2 = var _1=RELATED('Timesheet Weekly Summary'[Week Ending Date]) return IF( 'Timesheet Booking Data'[Date] <='Timesheet Booking Data'[Flag],'Timesheet Booking Data'[Flag],MAX('Timesheet Booking Data'[Flag]))Date1 = IF( 'Timesheet Booking Data'[Date]<='Timesheet Booking Data'[Flag2],'Timesheet Booking Data'[Flag2],MAX('Timesheet Weekly Summary'[Week Ending Date]))all hours per working week = CALCULATE(SUM('Timesheet Booking Data'[Hours]), FILTER(ALL('Timesheet Booking Data'), 'Timesheet Booking Data'[Date1]=EARLIER('Timesheet Weekly Summary'[Week Ending Date])&&'Timesheet Booking Data'[Week] in {4,5}))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Week = WEEKDAY('Timesheet Booking Data'[Date])Flag = RELATED('Timesheet Weekly Summary'[Week Ending Date])Flag2 = var _1=RELATED('Timesheet Weekly Summary'[Week Ending Date]) return IF( 'Timesheet Booking Data'[Date] <='Timesheet Booking Data'[Flag],'Timesheet Booking Data'[Flag],MAX('Timesheet Booking Data'[Flag]))Date1 = IF( 'Timesheet Booking Data'[Date]<='Timesheet Booking Data'[Flag2],'Timesheet Booking Data'[Flag2],MAX('Timesheet Weekly Summary'[Week Ending Date]))all hours per working week = CALCULATE(SUM('Timesheet Booking Data'[Hours]), FILTER(ALL('Timesheet Booking Data'), 'Timesheet Booking Data'[Date1]=EARLIER('Timesheet Weekly Summary'[Week Ending Date])&&'Timesheet Booking Data'[Week] in {4,5}))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
I have just implemented the above in my dataset with the following changes being made
1. Week ending date is a friday
2. Date settings is English - United Kingdom
3. Extended data
The Date 1 calculated column is no longer working as intended. Any ideas where I have gone wrong?
Screenshot below for reference.