Return Column Name of Last Non-zero value in row range

[Anonymous]

Hi all - I am attempting to recreate a formulated column I have in excel to DAX language.  I have had a lot of luck googling other power bi queries, but I can't find anything on this.  I am using the lookup function in excel, but i can't find the equivalent in DAX.  Dax has a "lookupvalue" fuction but this does not seem to work the same.

The custom column needs to be populated by the "column name" corresponding to the last "non-zero" value in a row.  Example is below of the excel version of the formula.  Does anyone know how to make this same calculation with a custom DAX column in Power BI?  

=LOOKUP(2,1/(Table1[@[FY2021]:[FY2031]]<>0),RIGHT(Table1[[#Headers],[FY2021]:[FY2031]],4))

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

In order to create the table like above, the first step that I generally take is to transform the table like below.

Then, the lookup tables are created (in general), and the relationships can be created like below.

All measures for creating the below visualization are in the sample pbix file, and the link is down below.

https://www.dropbox.com/s/rm9xeguaegqvpc5/ali.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM