Forum Discussion
PowerBI Model time dependant
Hi PGG79 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin and SundarRaj for the response.
I have worked on your sample data and attached the PBIX file with the results. Please review it and let us know if it is helpful. If you have any further questions, please provide more details.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
Hi lbendlin
When I posted the question it was with a short example, I try to explain it a bit better. I also add another table I have.
- I have Technicians (TechniciansDetails). The information of the Technicians can be updated at any time. The information is: Organization to where they belong to, the job position, the costarea to where they belong....
For each change in the information of a Technician, there is a record with "start" and "end date" in the table. The active one is the one with "end date = 31/12/999".
- The Technicians can work based on different Working Schedules (TechnicianWorkingSchedule). The working schedule of the Technicians can be updated at any time. Each time a Technician changes Working Schedule, there is a record with "start" and "end date". The active one is the one with "end date = 31/12/999".
- The Technicians record the times they spent per working day in each topic (7 hours of work, 1 hour of travel, 1 hour of lunch,...) (TechnicianHoursRecorded). For each working day the technicians record: their hours, in which project, ... For each time recorded, there is an entry in the table with the date when the entry was done.
--> It could be that for each working day, the technician have a different job position or a different calendar.
I need to have a model that helps me to report by working day, the recording hours of the technicians and more important: what working schedule they have at that day (please note that working schedule can change and have a start and end date) and what details (eg. job position) they had.
Thank you.
(I thought i could add documents but i cannot, so i put here the tables)
TechnicianDetails
| Technician | End Date | Start Date | Organization | CostArea | JobPosition | Technician name |
| Tech1 | 9999-12-31 | 2023-01-01 | Organization1 | costarea1 | JobPosition3 | Tech1 |
| Tech2 | 9999-12-31 | 2023-01-15 | Organization1 | costarea2 | JobPosition1 | Tech2 |
| Tech2 | 2023-01-14 | 2022-12-04 | Organization3 | costarea3 | JobPosition2 | Tech2 |
| Tech3 | 9999-12-31 | 2023-01-01 | Organization2 | costarea2 | JobPosition3 | Tech3 |
| Tech4 | 9999-12-31 | 2023-02-01 | Organization2 | costarea3 | JobPosition4 | Tech4 |
WorkingSchedule
| Technician | End Date | Start Date | WorkingSchedule | MoHrs | Wk.hrs. | Hrs/Day | WkDys | AnnualHrs |
| Tech1 | 9999-12-31 | 2023-02-01 | Calendar1 | 164 | 40 | 8 | 5 | 1.968,00 |
| Tech1 | 2023-01-31 | 2023-01-01 | Calendar2 | 102,5 | 25 | 5 | 5 | 1.230,00 |
| Tech2 | 9999-12-31 | 2023-01-02 | Calendar2 | 102,5 | 25 | 5 | 5 | 1.230,00 |
| Tech2 | 2023-01-01 | 2022-12-04 | Calendar3 | 98,4 | 24 | 6 | 4 | 1.180,80 |
| Tech3 | 9999-12-31 | 2023-02-15 | Calendar1 | 164 | 40 | 8 | 5 | 1.968,00 |
| Tech3 | 2023-02-14 | 2023-01-01 | Calendar3 | 98,4 | 24 | 6 | 4 | 1.180,80 |
| Tech4 | 9999-12-31 | 2023-02-01 | Calendar1 | 164 | 40 | 8 | 5 | 1.968,00 |
RecordedHours
| Technician | Recording Date | Hours | HourType | project | Registered on |
| Tech2 | 09/12/2022 | 3 | Work | project1 | 23/12/2022 |
| Tech2 | 22/12/2022 | 1 | Travel | project4 | 23/12/2022 |
| Tech1 | 09/01/2023 | 1 | Work | project3 | 20/01/2023 |
| Tech1 | 10/01/2023 | 1 | Work | project4 | 20/01/2023 |
| Tech1 | 11/01/2023 | 1 | Travel | project5 | 20/01/2023 |
| Tech1 | 03/02/2023 | 8,5 | Work | project3 | 15/02/2023 |
| Tech1 | 21/02/2023 | 3,5 | Work | project4 | 15/02/2023 |
| Tech3 | 26/01/2023 | 2 | Lunch | project2 | 22/01/2023 |
| Tech3 | 03/01/2023 | 8,5 | Work | project2 | 22/01/2023 |
| Tech3 | 04/01/2023 | 8,5 | Work | project3 | 22/01/2023 |
| Tech3 | 20/02/2023 | 8,5 | Work | project3 | 15/02/2023 |
| Tech4 | 13/02/2023 | 5 | Work | project1 | 17/02/2023 |
| Tech4 | 14/02/2023 | 5 | Work | project3 | 17/02/2023 |
Your WorkingSchedule table is inconclusive. You need to specify which days of the week , and what the business hours are. Same for the RecordedHours - needs a start and end time. Otherwise you don't know when they worked outside of the agreed days/hours.
Hi lbendlin
that's the way the information is coming from the source system. The assumption is that the recording is done in working days within business hours. For the report we don't care.
for me the main problem is how I can do the model to know the workingschedule valid for the day the hours were recorded. Thank you!
I cannot assist you with that in a meaningful way.I hope someone else can help you further.