Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
PGG79
Regular Visitor

PowerBI Model time dependant

Hi

I have two tables.

In one I have the technicians' hours recorded on a date, here is the example:

TechnicianRecording dateHours 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:

TechnicianWorking ScheduleActive 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!

8 REPLIES 8
v-sathmakuri
Community Support
Community Support

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!!

SundarRaj
Resolver III
Resolver III

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!

SundarRaj_0-1744750453326.png

SundarRaj_1-1744750479407.png

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!

lbendlin
Super User
Super User

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

TechnicianEnd DateStart DateOrganizationCostAreaJobPositionTechnician name
Tech1   9999-12-312023-01-01Organization1costarea1JobPosition3Tech1   
Tech2   9999-12-312023-01-15Organization1costarea2JobPosition1Tech2   
Tech2   2023-01-142022-12-04Organization3costarea3JobPosition2Tech2   
Tech39999-12-312023-01-01Organization2costarea2JobPosition3Tech3
Tech49999-12-312023-02-01Organization2costarea3JobPosition4Tech4

 

WorkingSchedule

TechnicianEnd DateStart DateWorkingSchedule  MoHrsWk.hrs.Hrs/DayWkDysAnnualHrs
Tech1   9999-12-312023-02-01Calendar116440851.968,00
Tech1   2023-01-312023-01-01Calendar2102,525551.230,00
Tech2   9999-12-312023-01-02Calendar2102,525551.230,00
Tech2   2023-01-012022-12-04Calendar398,424641.180,80
Tech39999-12-312023-02-15Calendar116440851.968,00
Tech32023-02-142023-01-01Calendar398,424641.180,80
Tech49999-12-312023-02-01Calendar116440851.968,00

 

RecordedHours

TechnicianRecording Date HoursHourTypeprojectRegistered on
Tech2   09/12/20223Workproject123/12/2022
Tech2   22/12/20221Travelproject423/12/2022
Tech1   09/01/20231Workproject320/01/2023
Tech1   10/01/20231Workproject420/01/2023
Tech1   11/01/20231Travelproject520/01/2023
Tech1   03/02/20238,5Workproject315/02/2023
Tech1   21/02/20233,5Workproject415/02/2023
Tech326/01/20232Lunchproject222/01/2023
Tech303/01/20238,5Workproject222/01/2023
Tech304/01/20238,5Workproject322/01/2023
Tech320/02/20238,5Workproject315/02/2023
Tech413/02/20235Workproject117/02/2023
Tech414/02/20235Workproject317/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.

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082