Calendar and Weekly exchange rate

[gomezc73]

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 necesary use the last valid exchange rate downloaded.

 By Example:

   The exchange rate used for the day 07/13/2020 must be 1.131850 (Downloaded on 07/11/2020). that is the last vlid exchange rate

    Somebody know how do it?

This is the table i have:

Date	Currency From	Currency To	Date IN JDE	Exchange Rate
7/10/2020	EUR	USD
7/11/2020	EUR	USD	7/11/2020	1.131850
7/12/2020	EUR	USD
7/13/2020	EUR	USD
7/14/2020	EUR	USD
7/15/2020	EUR	USD
7/16/2020	EUR	USD
7/17/2020	EUR	USD
7/18/2020	EUR	USD	7/18/2020	1.142250
7/19/2020	EUR	USD
7/20/2020	EUR	USD
7/21/2020	EUR	USD
7/22/2020	EUR	USD
7/23/2020	EUR	USD
7/24/2020	EUR	USD
7/25/2020	EUR	USD	7/25/2020	1.162550

[amitchandak · Accepted solution]

@gomezc73 , locate the attachment with the solution.

[gomezc73]

Hello, I tried using

New exchange Rate = maxx(filter('Table', [Date]<=EARLIERER('Table'[Date])),LASTNONBLANKVALUE('Table'[Date IN JDE],max('Table'[ Exchange Rate ]))))

And it works only with a "Currency From", By Example if the tables is only From EUR to USD.

But when i added other Currencys in the tables the formula doesn't work. It bring the MAX Exchange rates From other Currencys..

How The formula can be improve to consider the "Currency From"?

[gomezc73]

Perfect!!!. That's just what i needed.. You are a genious!!

[Ashish_Mathur]

Hi,

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

[gomezc73]

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 Date	Currency From	Currency To	Download Date	Exchange Rate
1/6/2018	AED	USD	1/6/2018	0.272300
1/7/2018	AED	USD		0.272300
1/8/2018	AED	USD		0.272300
1/9/2018	AED	USD		0.272300
1/10/2018	AED	USD		0.272300
1/11/2018	AED	USD		0.272300
1/12/2018	AED	USD		0.272300
1/13/2018	AED	USD	1/13/2018	0.272300
1/14/2018	AED	USD		0.272300
1/15/2018	AED	USD		0.272300
1/16/2018	AED	USD		0.272300
1/17/2018	AED	USD		0.272300
1/18/2018	AED	USD		0.272300
1/19/2018	AED	USD		0.272300
1/20/2018	AED	USD	1/20/2018	0.272300

[Greg_Deckler]

@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. 

[Greg_Deckler]

@gomezc73 - You are basically looking at Lookup Min/Max kind of - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

In your case,

Measure =
  VAR __Date = MAX('Table'[Date])
  VAR __RateDate = MAXX(FILTER('ExchangeRates',[Date]<=__Date && [Exchange Rate] <> ""),[Date])
RETURN
  MAXX(FILTER('ExchangeRates',[Date] = __RateDate),[Exchange Rate])