Forum Discussion
PowerBI Model time dependant
Hi
I have two tables.
In one I have the technicians' hours recorded on a date, here is the example:
| Technician | Recording date | Hours recorded |
| Tech1 | 2023/01/01 | 8 |
| Tech2 | 2023/01/02 | 7 |
| Tech1 | 2023/01/03 | 6 |
| Tech3 | 2023/01/04 | 5 |
| Tech4 | 2023/02/01 | 7 |
| Tech1 | 2023/02/01 | 8 |
| Tech2 | 2023/02/02 | 7 |
| Tech1 | 2023/02/03 | 6 |
| Tech1 | 2023/03/03 | 5 |
In the other table I have the technicians and their working schedule active from an specific date (hours, costs, conditions,... are different depending on the calendar), here is the example:
| Technician | Working Schedule | Active since |
| Tech1 | Calendar1 | 2023/01/01 |
| Tech2 | Calendar2 | 2023/01/02 |
| Tech3 | Calendar3 | 2023/01/03 |
| Tech1 | Calendar2 | 2023/01/01 |
| Tech4 | Calendar3 | 2023/02/01 |
I want to create a Power BI model that allows me to generate a report where I can see the recorded hours of the technicians, and the working schedule active on that moment.
Probably it is an easy solution but i cannot come up with it. Thank you so much!
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!!
13 Replies
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!!
Your sample data is not clear to me. What would be the expected outcome based on the sample data you provided?
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 PGG79 , is this what you are looking for? I'll attach the images of the output and M code used. Let me know if I understood your query correctly. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name = "Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician", type text}, {"Recording date", type date}, {"Hours recorded", Int64.Type}}),
LookUp = Table.AddColumn(#"Changed Type","LookUpVal",each [Technician] & "-" & Text.From([Recording date])),
Table = Excel.CurrentWorkbook(){[Name = "Table6"]}[Content],
Type = Table.TransformColumnTypes(Table,{{"Technician", type text}, {"Working Schedule", type text}, {"Active since", type date}}),
Position = Table.AddColumn(Type,"HoursRecorded",each List.PositionOf(LookUp[LookUpVal],[Technician] & "-" & Text.From([Active since]))),
#"Recorded Hours" = Table.TransformColumns(Position,{"HoursRecorded", each try LookUp{_}[Hours recorded] otherwise null})
in
#"Recorded Hours"Hi SundarRaj
thank you for the quick answer and for the idea, but that solution is based on a vlookup and that means that if the match of "technician+startdate" with "technician+recording date" is not exact, the result is "null". Please check the answer i have given to Ibendlin that contains more details because when i did the first post it was a shorten example but the tables have more fields. Thank you for your support!
Hi PGG79 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi PGG79 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you!!
Hi PGG79 ,
I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.
Thank you!!
