LookupValue

[pelucapampa]

Hi, I've a Table with exchange Rates and Sales Table.

My Exchange Rate, don't have value for a weekends days, when I insert a New Column into a Sales Table with Exchange rate, I cannot obtain Exchange Rate for weekends day.

Have you any Ideas to search Friday Exchange Rate for weekends days?

[v-cherch-msft]

Hi @pelucapampa

You may try to use LOOKUPVALUE Function. For example: 1-7 in 'day of week' column is from Monday to Sunday.

You may get the 'Lookup' column by calculated column:

Lookup =
IF (
 ISBLANK ( LOOKUPVALUE ( 'Rate'[Rate], Rate[day of week], Sales[day of week] ) ),
 MAXX ( FILTER ( Rate, Rate[day of week] > Rate[day of week] - 1 ), Rate[Rate] ),
 LOOKUPVALUE ( 'Rate'[Rate], Rate[day of week], Sales[day of week] )
)

If it is not your case, please share some data sample and expected output. 

Regards,

Cherie

[pelucapampa · Accepted solution]

Thanks for your reply, I resolved!!!

I'm copying my resolve:

ExchRate1 =
IF(
    ISBLANK(LOOKUPVALUE('Exchange Rate'[Precio],'Exchange Rate'[Fecha],EstadVentas[Date])),
         IF(
            ISBLANK(LOOKUPVALUE('Exchange Rate'[Precio],'Exchange Rate'[Fecha],DATEADD(EstadVentas[Date],-1,1,DAY))),
            LOOKUPVALUE('Exchange Rate'[Precio],'Exchange Rate'[Fecha],DATEADD(EstadVentas[Date],-2,1,DAY)),
            LOOKUPVALUE('Exchange Rate'[Precio],'Exchange Rate'[Fecha],DATEADD(EstadVentas[Date],-1,1,DAY))),
            LOOKUPVALUE('Exchange Rate'[Precio],'Exchange Rate'[Fecha],EstadVentas[Date])
            )