How to create a new column to get the latest record per id prior to the record date

[jmowen]

Hello,

I'm having a bit of trouble finding a solution to this problem.

For every "ID" I would like to state what their "Type" was at the given date for each "Jobtype"

I have 2 "jobtype" "S" and "H" when information for "jobtype" "H" is filled out, the "Type" is not recorded. However, that information is recorded in "jobtype "S" 

So what I would like to do is for every "Jobtype" that is "H" Find the Latest "Type" for that "ID" in "Jobtype" "S" prior to the "Date" of when "Jobtype" "H" was done.

example:
"ID" "1" had a "jobtype" "H" carried out on the 1/02/2022, to return the correct value you would find the latest record of "jobtype" "S" with an "ID" of "1" that is before 1/02/2022 and return the "Type" in this case it would be "A" 

Now "ID" "1" also had jobtype" "H" carried out on the 6/05/2022 to return the correct value you would find the latest record before 6/05/2022 which would be 12/03/2022, "Type" "B"

Hopefully I've explained my issue well enough. Below is an image of the current data and what the desired outcome would be.

Data

Job ID	ID	Jobtype	Date	Type	Score
1	1	S	9/05/2021	A	1050
2	1	S	9/05/2021	B	1050
3	2	S	9/05/2021	A	500
4	2	S	10/05/2021	B	2561
5	3	S	10/05/2021	A	1590
6	3	S	10/05/2021	B	600
7	4	S	11/05/2021	B	2842
8	5	S	11/05/2021	B	1983
9	1	H	1/02/2022
10	3	H	2/02/2022
11	4	H	3/02/2022
12	1	S	12/03/2022	B	237
13	3	S	13/03/2022	A	1020
14	1	H	6/05/2022
15	3	H	12/05/2022

Outcome

Job ID	ID	Jobtype	Date	Type	Score	Type 2
1	1	S	9/05/2021	A	1050
2	1	S	9/05/2021	B	1050
3	2	S	9/05/2021	A	500
4	2	S	10/05/2021	B	2561
5	3	S	10/05/2021	A	1590
6	3	S	10/05/2021	B	600
7	4	S	11/05/2021	B	2842
8	5	S	11/05/2021	B	1983
9	1	H	1/02/2022			B
10	3	H	2/02/2022			A
11	4	H	3/02/2022			B
12	1	S	12/03/2022	B	237
13	3	S	13/03/2022	A	1020
14	1	H	6/05/2022			B
15	3	H	12/05/2022			A

[lbendlin]

Type 2 = 
if([Jobtype]="H",
var i = [ID]
var d = [Date]
var a = SUMMARIZE(FILTER('Data','Data'[ID]=i && 'Data'[Date] < d && 'Data'[Jobtype]="S"),'Data'[Job ID],'Data'[Score],'Data'[Type])
var b = maxx(a,[Score])
var c = filter(a,[Score]=b)
var e = TOPN(1,c,[Job ID],DESC)
return concatenatex(e,[Type])
)

or if you insist on using EARLIER

Type 2 = 
if([Jobtype]="H",
var a = SUMMARIZE(FILTER('Data','Data'[ID]=EARLIER(Data[ID]) && 'Data'[Date] < EARLIER(Data[Date]) && 'Data'[Jobtype]="S"),'Data'[Job ID],'Data'[Score],'Data'[Type])
return concatenatex(TOPN(1,filter(a,[Score]=maxx(a,[Score])),[Job ID],DESC),[Type])
)

[jmowen]

Thanks for this it works great!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Type 2 = if(Data[Jobtype]="S",BLANK(),lookupvalue(Data[Type],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(data,Data[ID]=EARLIER(Data[ID])&&Data[Jobtype]="S"&&Data[Date]<EARLIER(Data[Date]))),Data[ID],Data[ID]))

Hope this helps.

[jmowen]

Thanks! works greats. I'll accept this solution as it did solve my intinal problem.
I did run into an problem when using my real data however and it just comes down having multiple values returned.  I came up with a way to decide what value to keep based on some additional. If you don't mind taking a look? or would it be better to open another disscussion? 

The outcome I would like is if there are multiple Types returned it will return the Type that has the Highest score and if the score is the same it will return the Type with the highest Job ID

I've replicated the issue in the data below. 

Data

Job ID	ID	Jobtype	Date	Type	Score
1	1	S	9/05/2021	A	1050
2	1	S	9/05/2021	B	1050
3	2	S	9/05/2021	A	500
4	2	S	10/05/2021	B	2561
5	3	S	10/05/2021	A	1590
6	3	S	10/05/2021	B	600
7	4	S	11/05/2021	B	2842
8	5	S	11/05/2021	B	1983
9	1	H	1/02/2022
10	3	H	2/02/2022
11	4	H	3/02/2022
12	1	S	12/03/2022	B	237
13	3	S	13/03/2022	A	1020
14	1	H	6/05/2022
15	3	H	12/05/2022

Outcome

Job ID	ID	Jobtype	Date	Type	Score	Type 2
1	1	S	9/05/2021	A	1050
2	1	S	9/05/2021	B	1050
3	2	S	9/05/2021	A	500
4	2	S	10/05/2021	B	2561
5	3	S	10/05/2021	A	1590
6	3	S	10/05/2021	B	600
7	4	S	11/05/2021	B	2842
8	5	S	11/05/2021	B	1983
9	1	H	1/02/2022			B
10	3	H	2/02/2022			A
11	4	H	3/02/2022			B
12	1	S	12/03/2022	B	237
13	3	S	13/03/2022	A	1020
14	1	H	6/05/2022			B
15	3	H	12/05/2022			A

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.  Try these calculated column formulas

To consider = if(Data[Jobtype]="S",if(CALCULATE(countrows(Data),filter(Data,Data[ID]=earlier(Data[ID])&&Data[Jobtype]="S"))=1,1,if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Date]<EARLIER(Data[Date]))),1,if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Date]=EARLIER(Data[Date])&&Data[Score]<EARLIER(Data[Score]))),1,if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Date]=EARLIER(Data[Date])&&Data[Score]=EARLIER(Data[Score])&&Data[Job ID]<EARLIER(Data[Job ID]))),1)))),BLANK())

Type 2 = if(Data[Jobtype]="S",BLANK(),lookupvalue(Data[Type],Data[To consider],1,Data[ID],Data[ID]))

Hope this helps.

[lbendlin]

Please provide sample data in usable format, not as a screenshot.  Screenshot is ok for expected outcome.