Recently, while working on a project I came across an interesting problem.
The Scenario – Let’s say a company is pretty aggressive in conducting training interventions for it’s employees. On the flip side, their manager is always worried about “what if we train and they leave?”
My job was to find out the trained employees leaving the company
Consider this Emp Data..
- Each row is a single employee
- With standard columns like- E code, Name, Dept, DOJ, Last Day etc..
- The Last Day will only exist if the employee has left the company else blank
Now consider this Training Data..
- One employee can undergo many training programs
- No employee will undergo a training after his / her Last Day
The manager needs to find, how many people left the company within 3 months of the training date
A few more things..
- The manager would like to filter the result by Month, Qtr or Year
- The result can also be filtered by any Emp Table dimension (dept, business function, band etc..)
- Try to come up with a single measure. Use NO table/helper columns
- When you post your answer in the comments, also do post the relationship diagram of your model
- And yeah.. should you need a calendar / date table, you can draw one for yourself or take it from here
Update (21st July 2019) – Here is my answer
Trained and Left = CALCULATE ( COUNTROWS ( EmpData ), FILTER ( 'EmpData', VAR LastDay = CALCULATE ( MAX ( EmpData[Last Day] ), ALLEXCEPT ( EMPdata, EmpData[E.Code] ) ) RETURN IF ( LastDay <> BLANK (), CALCULATE ( MAXX ( CALCULATETABLE ( RELATEDTABLE ( Training ), DATESBETWEEN ( Cal[Date], EDATE ( LastDay, -3 ), LastDay ) ), Training[Training Date] ) ) ) ), USERELATIONSHIP ( Cal[Date], EmpData[Last Day] ) )
Download the Solved Power BI File – I am pretty sure my code can be optimized further. Feel free to nit pick in the comments