Map Dates to Employee Life Cycle | Power BI Challenge

today we are going to be doing a very interesting power bi challenge this challenge comes to you from aji who happens to be one of the students in my past live training sessions and he would want to map the dates in the employee life cycle journey now obviously that title wouldn't make any sense because i created that title for describing the problem but let's just take a look at the data and hopefully you'll be able to solve it let's go okay people i'm in this excel file which is where i have the data let me take two minutes to explain you the data and we'll then take a look at the problem that you have to solve so real quick we have four columns here the event the client the employee name and the date the state is nothing but the start date now let's just talk about individual columns and what do they represent the event actually means the life cycle stage of any particular employee so if the employee is moving from stage one to stage two consider events like promotion training signing up a new project things like that these are all events this is the name of the client for which the event is happening this is the name of the employee which is being considered for that event and this is the start date of that event now obviously if i talk about the different columns of the data it would not really make any sense but let me just actually pick up one particular employee take you through through his employee lifecycle journey and hopefully you'll be able to make sense of the data let's just consider brian lee for the moment now since this particular data has been sorted in the descending order of the date so you can see that the smallest date is always going to come at the bottom and the largest date is always going to come at the top so i'll be reading the data from bottom up okay so brian um went into an employee onboarding for client 2 and that employee onboarding started on the 6th of september pretty good now that particular employee onboarding finished on the same day on the 6th of september how do i know that because on the 7th of september which is the next day he started working on training on boarding for client number 2 right here now that particular training onboarding which is another event uh ended on the 8th of february 2022 right a couple of months from september how do i know that because on the 9th of february brian was now working for client 4 um under some event called shadow shift milestone whatever the name of the event that doesn't matter so that's how you would read the data these dates are nothing but the start of the event dates all right now let's just take a look at the problem that we have to solve all right i'm in power bi i have generated the output and that's what i'm expecting you to do it as well now the output expectation is that i should be able to pick up any particular date and take a look that on that particular date the employee was working on what task or what event and for which client as simple as that so i have built the same pivot table which is where i have the exact four columns of the data the date the client the event and the name and i also have a date slicer which is where i have picked up three dates for now so i'm able to take a look at on the 7th of feb on the 8th of feb and on the 9th of feb on these three dates what people of the company were working on what event for which client as simple as that all right that's been it i hope i made sense and you were able to understand the problem the data of course can be downloaded underneath the video and couple of rules before i leave you with the problem rule number one is that you're free to solve this using exclusively using power query or tax or maybe a mix of both using some fancy data modeling approaches so please feel free to just pick up any of your approaches and provide the answer and of course you have to put your answers or your tax code or your m code or your combination of both in the comment section of my blog and i'm gonna pick up the best solutions and discuss those as well and just like the way that we always do it i'm gonna give a big shout out to everybody who participates and posts the right answer and i'm gonna be seeing you once again with the answers very very soon thanks so much and i look forward for your participation i'll catch you guys in the next one [Music] you

Channel: Goodly

Views: 4,440

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: uKm8CgukeAg

Length: 4min 6sec (246 seconds)

Published: Tue Jun 21 2022