Forum Discussion

gomezc73's avatar
gomezc73
Icon for Helper V rankHelper V
6 years ago
Solved

Calendar and Weekly exchange rate

Hello, I need help with this issue.   I have a weekly exchange rate report, it is downloaded every saturday..   Then in the report if I need a exchange rate of Monday or Tuesday by example is ne...
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

In the report which you download every Saturday, is there a "Date of download" column?

  • gomezc73's avatar
    gomezc73
    Icon for Helper V rankHelper V
    6 years ago

    Hello

    Yes, the first column is the calendar date, and the fourth column is the date on which the Exchange Rate was obtained.

    when I search for

    • the calendar date,
    • from and
    • CURRENCY UP,

    it only gets me the exchange rate of the Saturdays and the rest of days is blank, so I need to repeat that exchange rate for the next few days, until I get the exchange rate for the next Saturday and so on.

    Calendar DateCurrency FromCurrency ToDownload Date Exchange Rate
    1/6/2018AEDUSD1/6/2018 0.272300
    1/7/2018AEDUSD 0.272300
    1/8/2018AEDUSD 0.272300
    1/9/2018AEDUSD 0.272300
    1/10/2018AEDUSD 0.272300
    1/11/2018AEDUSD 0.272300
    1/12/2018AEDUSD 0.272300
    1/13/2018AEDUSD1/13/2018 0.272300
    1/14/2018AEDUSD 0.272300
    1/15/2018AEDUSD 0.272300
    1/16/2018AEDUSD 0.272300
    1/17/2018AEDUSD 0.272300
    1/18/2018AEDUSD 0.272300
    1/19/2018AEDUSD 0.272300
    1/20/2018AEDUSD1/20/2018 0.272300

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      gomezc73 - Can you use Fill Down in Power Query? Otherwise, if you are doing a column, you could do something like:

       

      Final Exchange Rate =
  IF(
    [Exchange Rate] <> "",
    [Exchange Rate],
    VAR __Date = MAXX(FILTER('Table',[Calendar Date] < EARLIER([Calendar Date]) && [Currency From] = EARLIER([Currency From]) && [Currency To] = EARLIER([Currency To]) && [Exchange Rate] <> ""),[Calendar Date])
    RETURN MAXX(FILTER('Table',[Calendar Date] = __Date),[Exchange Rate])

      Somehow though, not sure we are all starting from the same place you are.