cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper IV

## 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 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

1 ACCEPTED SOLUTION
Super User

@gomezc73 , locate the attachment with the solution.

7 REPLIES 7
Super User

@gomezc73 , locate the attachment with the solution.

Helper IV

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"?
` `
Helper IV
`Perfect!!!. That's just what i needed.. You are a genious!!`
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

@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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors