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.
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|
Solved! Go to Solution.
Hello, I tried using
In the report which you download every Saturday, is there a "Date of download" column?
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
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|
@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.
@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])
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.