FX Rate Calculation

The following is an example Forward trade extracted using SQL and the scalar function used to retrieve the GBP FX Rate for the principal and quoting currencies on the date of the trade.

The record is recreated in Power BI showing the DAX used to retrieve the same FX Rates.

The following example is a ficticious currency forward trade between two counterparties:

Counterparty:  Mirierck Bank agrees a future currency sale with Adoro Bank plc.

Action: Sell 300,000 Euros (converts to 272430.08 GBP at the agreed strike price)

Trade Matures: 2019-07-16

Agreed Strike Price: 1.10119998496495

Adoro Bank plc buys Euros with the 272430.08 GBP from the market at 1.10739999782697 giving 301689.07 EUR, which gives them a profit of £1689.07 Euros.

To extract the trade records

The database scalar function to get the fx rate.

The DAX calculation to get the fx rate.

The same trades represented in Power BI.

The column names have been kept the same as those in the database for the purposes of this example.