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 Exchange Rate for weekends day.

 

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

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

2 Replies

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