LOOKUP first date in remote table

[LUCASM]

I am trying to get a (fictitious) first date from my 'Calendar' table where I only have a [Fiscal Year Quarter] in my source data.

My 'Calendar' table contains a complete running table of [Date], [Fiscal Year Quarter] and other useful date elements.

So if in my source data set [Fiscal Year Quarter] = FY24 Q1

Then I need to return 1/4/2024 (1 April 2024)

This assumes that my Fiscal Year Starts 1 April 

[ryan_mayu · Accepted solution]

@LUCASM 

is this what you want?

Column =

 VAR _q=right('Table'[Fiscal Year Quarter],1)*1

 VAR _m=_q*3+1

 return DATE(2000+MID('Table'[Fiscal Year Quarter],3,2)*1,_m,1)

[LUCASM]

It's not a lookup but it certainly solves my problem, Thank you

[ryan_mayu]

you are welcome