Calculated column first and last entries of different column

[Anonymous]

Hi,
I need to add a calculated column to a table that is in the tabulr model, I need a flag that checks each job whether the first entry (based on the date) of the value in column 'From Code' is the same as
the last entry of column 'To Code' (based on the date).
I need the outcome looks like below. Job111 to be flage as 'Yes' becaue the value of 'From Code' = MA is the same as the last entry of 'To Code'= MA.

Or even a measure, it does not have to be calculated column.

Thanks in advance

[Ashish_Mathur]

Hi,

Write this calculated column formula

Flag = if(LOOKUPVALUE(Data[From Code],Data[Created DateTime Local],CALCULATE(min(Data[Created DateTime Local]),FILTER(Data,Data[Job Number]=EARLIER(Data[Job Number]))),Data[Job Number],Data[Job Number])=LOOKUPVALUE(Data[To Code],Data[Created DateTime Local],CALCULATE(max(Data[Created DateTime Local]),FILTER(Data,Data[Job Number]=EARLIER(Data[Job Number]))),Data[Job Number],Data[Job Number]),"Yes","No")

Hope this helps.

[Vera_33 · Accepted solution]

Hi @Anonymous 

You mean a DAX calculated column, right?

Flag = 
VAR CurJob = 'Table'[Job Number]
VAR T1 = FILTER('Table','Table'[Job Number]=CurJob)
VAR MinDate = MINX(T1,[Created DateTime Local])
VAR MaxDate = MAXX(T1,[Created DateTime Local])
VAR FromCode = MAXX(FILTER(T1,'Table'[Created DateTime Local]=MinDate),[From Code])
VAR ToCode = MAXX(FILTER(T1,'Table'[Created DateTime Local]=MaxDate),[To Code])
RETURN
IF(FromCode=ToCode,"Yes","No")

[Anonymous]

Brriliant, Thanks @Vera_33 , it worked.