All - not sure if this is a Power Query or DAX issue, but:
I have a table of employees employee changes, including promotions. Like below:
EmployeeID Employee Old Pay New Pay PositionIDOld PositionIDNew Created Date PositionTitleNew
1234 Smith, John 9 10 452 452 1/15/2021 Developer
2345 Doe, Jane 11 12 341 431 1/14/2022 Human Resources
3456 Doe, John 9 561 6/12/2022 Security
What I am trying to calculate is the average time employees are at their Old Pay before being promoted. Blanks indicate the employee was a new hire. I have two columns which calculate the months and years an individual employee has been in the position - that part is working fine.
What I can't figure out is the averages. They don't seem to averaging correctly - I am confirming the results in Excel and via a calculator, but the results aren't matching up. I removed all rows in which someone's Old Pay and New Pay are the same (which could be because they moved jobs but stayed at the same pay, changed their name, etc.).
The table I created will only show averages up to a pay 10, even though I know employees went up to a pay 11. Simiarly, when I filter by position, there are Pays on there which aren't accurate - for example, it will say the average time a Developer is at a Pay 8 is 11 months, but Pay 8 isn't a valid pay for a developer.
I tried changing the column to show the PositionTitleOld, but that didn't change the averages at all. Additionally, it left me with blank values because new hires don't have an old PositionTitleID.
If it helps, this is the calculated column I'm using to calculate months at the Pay level - it is working fine. I have a similar one for years at the pay level.
MonthsinGrade =
var _min = MINX(FILTER(PositionHistory, PositionHistory[EmployeeID] = EARLIER(PositionHistory[EmployeeID]) && PositionHistory[CreatedDate] > EARLIER(PositionHistory[CreatedDate])), PositionHistory[CreatedDate])
var _diff = DATEDIFF(PositionHistory[CreatedDate],COALESCE(_min,TODAY()),MONTH)
return MOD(_diff,12)
