Forum Discussion

LUCASM's avatar
LUCASM
Icon for Helper IV rankHelper IV
2 years ago
Solved

LOOKUP first date in remote table

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 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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)
     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    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)