Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Extract date from text

Hello All,

 

I have a stand alone table in Live connection to tabular model. I am trying to create a report level measure as i dont have access to Tabular model.

 

I have a column FX_DATEKEY but the value appears like below. I am trying to extract the date from that column, another catch is FX_DATEKEY is read only column.

 

Current FX rate USD        FX_DATEKEY
1.7888                         fxrates.br-20120101

 

I tried, this, 

DateFx =
RIGHT('HFM FX Rate Actual'[FX_DATEKEY],8)
 
But this is not working, can you please?
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Sorry for the miss direction but when handling with live connections on tabular models some expressions are not valid try the following:

     

    DateFx =
RIGHT ( LASTNONBLANK ( 'HFM FX Rate Actual'[FX_DATEKEY], 0 ), 8 )

    Regards,

    MFelix

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous ,

     

    Since you are using a measure you need to give grouping to your measure try to use the following measure:

    DateFx =
RIGHT(SELECTEDVALUE('HFM FX Rate Actual'[FX_DATEKEY]),8)

    Regards,

    MFelix

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so much for quickly looking into this.

       

      I tried the below, it fails at SELECTEDVALUE, i tried ALLSELECTED instead of SELECTEDVALUE, the error disappeared, however i got an error. "Calculation error in Measure: A table of multiple values was supplied where a single value was expected", can you please help?

       
      DateFx = 
RIGHT(SELECTEDVALUE('HFM FX Rate Actual'[FX_DATEKEY]),8
      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous ,

         

        Try to use the:

        DateFx = RIGHT(MAX('HFM FX Rate Actual'[FX_DATEKEY]),8)

         

        Regards,

        MFelix