Dax to calculate Time in Position ignoring an event/transaction

[bwarner87]

Hello Community,

First post and hoping it meets the standards of a good post 🙂  

Problem: Using DAX (measure or calculated column) I'm trying to calculate an employee's time in their latest position "Time in Position ignore JAC:" ignoring an event in the form of a transaction tied to a ["Reason_Code" = JAC] where they restructured job names that should NOT effect an employee's time in position calculation because essentially the job is the same but the title was updated with new codes and titles. Ultimatley i'm going to display this value for a single employee on an employee card.

Sample Data with 2 use cases using Excel to illustrate:

Use Case 1: the employee has NOT changed jobs and was part of the job title restructure.

Use Case 2: the employee HAS a valid job change that came after the job title restructure.  

Screen shot with details

Excel Format

Use Case 1
EmpID	Job_Code	Job_Title	Appt_Start_date	Reason_Code_Job	TimeInPosition
555555	1234	Director, HR Business Parter	9/1/2020	MER	0.833333333		Time in Position ignore JAC:	4.416667
555555	1234	Director, HR Business Parter	9/1/2020	JAC	0.833333333
555555	6566	Director B, HR	12/16/2017	ANN	3.583333333
555555	6566	Director B, HR	12/16/2017	MER	3.583333333
555555	2233	Director A, HR	10/20/2015	MER	5.75
555555	2233	Director A, HR	10/20/2015	COM	5.75
555555	4455	Manager Z, Employee relations	10/1/2011	ADJ	9.75
555555	4455	Manager Z, Employee relations	10/1/2011	MER	9.75
Use Case 2
EmpID	Job_Code	Job_Title	Appt_Start_date	Reason_Code_Job	TimeInPosition
666666	4000	SVP	4/1/2021	MER	0.25		Time in Position ignore JAC:	0.25
666666	1234	Director, HR Business Parter	9/1/2020	MER	0.833333333
666666	1234	Director, HR Business Parter	9/1/2020	JAC	0.833333333
666666	6566	Director B, HR	12/16/2017	ANN	3.583333333
666666	6566	Director B, HR	12/16/2017	MER	3.583333333
666666	2233	Director A, HR	10/20/2015	MER	5.75
666666	2233	Director A, HR	10/20/2015	COM	5.75
666666	4455	Manager Z, Employee relations	10/1/2011	ADJ	9.75
666666	4455	Manager Z, Employee relations	10/1/2011	MER	9.75

Note

- i highlighted in green the value i'm trying to solve for and gave the actual value it SHOULD be with the right calculation for both scenarios.

- all this data is in the same table/query in Power BI so don' worry about relationships.

What I've tried

- i'm working in Excel because if figured if i could get it to work there i could translate to DAX. 

- I first added the "TimeInPosition" column to calculate for each transaction against today's date using datedif function. You can see the formula in the formula bar for that transaction. 

- I then tried identying the latest position by looking at the min value of the TimeInPosition column which satisfies use case 2 but doesn't resolve use case 1 because use case 1 min value contains the event i want to ignore. 

Now i'm stuck and hoping you all can provide some guidance 🙂 Appreciate any insights. 

[bwarner87 · Accepted solution]

So after some time combing through the DAX formula documention and some testing against my use cases, I came up with a solution which I'll try my best to explain in layman's terms and then provide formulas.  

I realized i'm calculating a time in position for each employee, so I needed to move my row by row calculation in the form of a calculated column to my master employee data table.

I know how to calculcate a date difference from a date value and today's date but i needed to to determine my date value first. The date value I would need to calculate would need to look at a related table (two tables linked by a relationship; My master employee info linked to salary transactions table that contained Appt_Start_date) for a max value (or latest job appointment) of dates, in this case the job start date (Appt_Start_date) meaning the date that employee's job/position started. However because of our company structurally changing jobs for all employees,  I wanted to ignore those transactions because the employee's position didn't really change, the company changed how they labeled/named it. I needed to filter those rows and date values out before i did my max lookup. 

Solution formula 

Time in Position = DATEDIFF(MAXX(FILTER(RELATEDTABLE('Salary History (US)'),'Salary History (US)'[Action-Reason Code]<>"JRC-JAC"), 'Salary History (US)'[Effective_Job_Date]),TODAY(),MONTH)/12

DateDiff() - taking the date value i calculated and subracting it from today's date displayed by months in a calendar year.

MAXX() - for getting my max date value from a list of values from my related table that has been filtered

Filter() - for filtering out the transactions from the related table

RelatedTable() - identifying the table with a relationship to the table i was creating my calculated column in.

I hope this helps someone in the future!

[Greg_Deckler]

@bwarner87 I guess my question is, how do you know from the data what to include or not? Is MER the only reason code that should be considered? And when does this reset? In the examples, this number reset each time the Job_Code changed. Except in the latest job code change, it seems you want to preserve the time in position even though the job code changed. How do you know when and when not to reset even when the job code changes? Is it because of the JAC event, the job codes changed but the job actually stayed the same? So events prior to the JAC event should be treated one way and post the JAC another but only for the first couple of changes post JAC?

[bwarner87]

@Greg_Deckler 

Thanks for the prompt response. 

To answer your question, we would want to reset the Time In Position if there was a job code change UNLESS that job code change was a result of the Reason_Code_Job = JAC event. 

Thank you, 

Brandon