Forum Discussion
Helper VCalendar 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 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 |
@gomezc73 , locate the attachment with the solution.
7 Replies
- Greg_Deckler
Community Champion
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]) - Ashish_Mathur
Super User
Hi,
In the report which you download every Saturday, is there a "Date of download" column?
gomezc73Hello
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.
- amitchandak
@gomezc73 , locate the attachment with the solution.
- gomezc73
Perfect!!!. That's just what i needed.. You are a genious!! - 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"?
