cancel
Showing results for 
Search instead for 
Did you mean: 
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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors