Lookup and Max Value Help

[Anonymous]

Hi All,

I would like to lookup a value based on the max date and employee job number and return it to a new column for validation.

Employee ID	Job Number	Unique ID	Name	Date	Pay Cycle	Pay Validation
12345	0	123450	Bob	20/04/2020	Week	Week
12345	0	123450	Bob	23/11/2020	Week	Week
12345	0	123450	Bob	15/03/2021	Fortnight	Week
12345	1	123451	Bob	26/10/2020	Week	Week
12345	1	123451	Bob	20/10/2020	Fortnight	Week

Unique ID - combination of Employee ID and Job number

Employees can have multiple jobs (0 primary, 1 secondary). I am trying to bring back the most recent secondary job pay cycle into a new column, "Pay Validation"

I can't seem to get this working within the same table.

Pay Validation =

VAR Latest_Time_ =
CALCULATE (
MAX ( table[Date] ),
FILTER ( ALL ( table[Unique ID] ), table[Unique ID] = table[Unique ID] )
)
RETURN
LOOKUPVALUE (
table[Pay Cycle],
table[Unique ID],table[Unique ID],
'table[Date], Latest_Time_
)

Thanks for the help

[Ashish_Mathur]

Hi,

For 123450, shouldn't the answer be fortnight?

[Anonymous]

I am using this for auditing purposes and so 123451 should align with 123450. Row 3 is the latest for 123450 and I can see that week <> fortnight and I can flag this. 

[Ashish_Mathur]

I am confused.  For 123450, the farthest date is 15/3/2021.  The pay scale for that is fortnight.  Therefore in the first four rows of the Pay validation column, shouldn't the answer be Fortnight?  Is my understanding correct?

[Anonymous]

Not quite.

I am trying to identify the difference in Pay Cycles for employees where their primary job (job number 0) is different to their secondary job (job number 1) and flag this as an issue as this needs to align i.e. if job 0 is fortnight then job 1 should be fortnight as well.

My thinking is to get the latest Pay Cycle using the latest date of employee ID job 1 and put it in the column Pay Validation.
From here there aim is to compare the latest date in job 0 and compare the output of Pay Cycle with Pay validation to see if there are any errors.

Does this help?

I have done the following which has sort of worked in the reverse
Pay Validation =
VAR myModel = 'Table'[Employee ID]
VAR MaxDate =
MAXX(
FILTER(
'Table',
'Table'[Employee ID] = EARLIER('Table'[Employee ID])
),'Table'[Date])
RETURN CALCULATE(MAX('Table'[Pay Cycle]),ALL('Table'),'Table'[Date] = MaxDate ,'Table'[Employee ID] = myModel)

I can then get the max date in a new column and compare max date = max date, and Pay Validation = Pay Cycle to see any inconsistencies.

I am open to suggestions on how to solve this.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[Pay Cycle],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Employee ID]=EARLIER(Data[Employee ID])&&Data[Job Number]=1)),Data[Employee ID],Data[Employee ID])

Hope this helps.

[Anonymous]

This is great, thank you so much for your help!

[Ashish_Mathur]

You are welcome.