Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IABKOmarISB
New Member

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

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):

CompanyYearCodeCompany's Code Last Year
Company A2001R-22 
Company B2001F-03 
Company C2001G-12 
Company A2002R-22R-22
Company B2002F-03F-03
Company C2002G-16G-12
Company A2003M-22R-22
Company B2003F-03F-03
Company C2003G-16G-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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@IABKOmarISB , Try a new column like

 

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@IABKOmarISB , Try a new column like

 

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

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

It works! Thanks a million!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors