Forum Discussion

pelucapampa's avatar
pelucapampa
Helper I
7 years ago
Solved

LookupValue

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 Exchan...
  • pelucapampa's avatar
    pelucapampa
    7 years ago

    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])
                )

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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's avatar
    pelucapampa
    Helper I
    7 years ago

    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])
                )