Extract date from text

[Anonymous]

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?

[MFelix]

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]

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]

Hi @Anonymous ,

Try to use the:

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

Regards,

MFelix

[MFelix · Accepted solution]

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

[Anonymous]

Thank you so much it worked.

[Anonymous]

It wasnt working and returns the error below.  😞