How to lookup a text value of the last year in a calculated column

[IABKOmarISB]

Hi Everyone,

I'm trying to use SAMEPERIODLASTYEAR to lookup a text string from last year but looks like this only works for numeric calculations. Here is a sample from my data model table (I want a DAX formula for the fourth column to return the values shown):

Company	Year	Code	Company's Code Last Year
Company A	2001	R-22
Company B	2001	F-03
Company C	2001	G-12
Company A	2002	R-22	R-22
Company B	2002	F-03	F-03
Company C	2002	G-16	G-12
Company A	2003	M-22	R-22
Company B	2003	F-03	F-03
Company C	2003	G-16	G-16

I want the fourth column to be a calculated column that answers the question (If this year is 2003, what was the company's code last year (2002)? I tried so many things but still can't figure out a formula to do it.

I would really appreciate the help.

[amitchandak · Accepted solution]

@IABKOmarISB , Try a new column like

maxx(filter(Table, [Company] = earlier([Company]) && [year] = earlier([year]) -1 ), [Code])

[IABKOmarISB]

Do you mind if I share your solution on the other forum that I posted the question on and credit you?

[IABKOmarISB]

It works! Thanks a million!