Forum Discussion
Help with Dates
Greetings everyone,
I have a list on the sharepoint where people put their time working during the day.
Basically, the database looks like this:
Timesheet
| Date | Hours | Resource | Activity |
| 01/01/2020 | 8 | R1 | A1 |
| 01/01/2020 | 7 | R2 | A2 |
| 01/01/2020 | 1 | R2 | A1 |
| 02/01/2020 | 10 | R2 | A3 |
I have another base that tells how many hours a day each resource has to work:
TabResources
| Resource | Hours per day |
| R1 | 8 |
| R2 | 8 |
I created a new table using Summarize to combine the hours, and also to add Hours per day usingRelated.
Summary = SUMMARIZE(Timesheet;Timesheet[Resource];Timesheet[Date];"Total Hours";SUM(Timesheet[Hours]))
Hours per day = RELATED(TabResources[Hours per day])
And created a matrix using Resources, Date and Total Hours to show how many hours each resource has input in a day.
Also, I created 2 cards. One with Total Hours and other with Hours per day.
But here's where my problem starts...
Considering the example, on 01/01/2020 R1 and R2 worked 8h each (16 hours in total). On 01/02/2020 only R2 worked, 10 hours.
The card "Total Hours" works fine...It shows the total hours entered for all resources. It shows 26 hours as total.
But the card "Hours per day" has a problem: It should show 32 hours:
- 8 hours of R1 in 01/01/2020
- 8 hours of R2 in 01/01/2020
- 8 hours of R1 in 02/01/2020
- 8 hours of R2 in 02/01/2020
But as R1 did not enter any data on 02/01/2020, the card does not consider the 8h that R1 should have been worked. So, it shows only 24h.
How can I solve this problem?
Best Regards,
Anonymous solution is attached, you can take it from here.
Would appreciate Kudos 🙂 if my solution helped.
9 Replies
Anonymous does that mean everyday each Resources hours get added regardless they worked or not? On 03rd, if R3 comes with 8 hrs per day , does that mean it will added 24 hours for 03rd (8 hours each R1, R2 and R3. Is this the business logic? Number of users x resource hours x number of dates?
Hello parry2k
In the perfect world, the resources should enter their hours in the Timesheet every day. But that doesn't always happen, sometimes the resource forgets doing the time tracking.
What I would like to have is a control of how many hours should have been entered, and how many hours were actually entered. So I can see the % of adherence to the system ... as well as the amount of overtime.
Basically: if I have 3 resources, in 1 business week they should work 8 hours per day. That is 3x8x5 = 120 hours
However, the catch is that some resources work 8 hours a day, while other resources work 6 hours. That's why I have the TabResource
Hi Anonymous ,
First, you can create one calendar table if it still not be created. Then create one measure as below to calculate the hours per date:
Hours per day = SUM ( 'TabResources'[Hours per day] ) * CALCULATE ( COUNTROWS ( 'Calendar' ), DATESBETWEEN ( 'Calendar'[Date], MIN ( 'Timesheet'[Date] ), MAX ( 'Timesheet'[Date] ) ), WEEKDAY ( 'Calendar'[Date], 2 ) < 6 )Best Regards
Rena