Hello People!
Recently, Aji (a fellow Power BI enthusiast and a participant in my LIVE Training) asked me this interesting question. Interesting enough that I am throwing it at you! Ready?
Map Dates to Employee Life Cycle – Video
Consider this Employee Life Cycle Data
- Event is the employee life cycle stage. For eg. promotion, lateral movement etc..
- Client & Emp Name are pretty self explanatory.
- Date is when the employee started the event.
- Data is always sorted by the Employee Name and Dates are sorted in descending order.
- The concatenation of 4 columns makes a unique row.
Let’s make things a bit more clear, consider Brian Lee’s story!
- Brain started Employee Onboarding for Client 2 on 6 Sept 2021 and finished the same day.
- On the next day, 7 Sep, he started with Training Onboarding for Client 2 which ended on 8 Feb 22.
- The next day, 9 Feb 2022, he started Shadow Shift Milestone for Client 4 which is currently ongoing.
Now here is the Problem!
I should be able to a select any date to see the Employees are working for which Clients.
Here is how the output should look like.
Isn’t that simple? Well it isn’t, hence this challenge!😂 Get going and show me your Power BI hubris.
Rules
- You are free to solve this exclusively using Power Query, DAX or a mix of both.
- Please don’t start fixing the data in excel. Everything should be done in the bounds of Power BI.
- Post your DAX / M Code in the comments along with a snapshot of the output.
- If your solution is a bit tricky, do share a link to download your pbix.
Solution
