cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kíeran Frequent Visitor
Frequent Visitor

Average Exchange Rates

Hi All,

 

I'm having an issue trying to calculate converted values using FX rates. In the model I have a base Value in the Booking table which is in a given currency (here, GBP and EUR) (denoted by the currency column), using this value and rates I'm trying to create two measures in the base ($) currency: Value YTD $ and Value ITD $ (Inception to Date).


The date dimension has months and days but the lowest granularity of time will actually be quarters. The currency conversion works as follows:

  • The exchange rate is always the YTD (date range selected) average for a given currency.
  • When reporting YTD Q1 2018 this should be the SUM(Q1 Values) * AVERAGE(Q1 Rates) for each currency.
  • When reporting YTD Q3 2018 this should be the SUM(YTD(Q3)) * AVERAGE(YTD(Q3 Rates)) for each currency.

I have created a simple measure for this:

 

Value ($) YTD = 
CALCULATE(
    TOTALYTD(SUM(Booking[Value]), 'Date'[Date]) * [Rate YTD Average]
)

 

However as you can see in the table below this doesn't work in row totals (it appears as if the average rate across all currencies is taken and not sub calculated per currency then totalled, this is probably a basic fix I know). I did get this working in another model correctly but honestly it looks like a hack and was brute forced.

Exchange Rate - Totals.png

 

I think the above is likely easily corrected, the ITD is causing more concern as the logic for the totals are as follows:

  • For inception to date reporting if the booking data starts at 2017 Q1 and ends 2018 Q4 and I filter date <= 2018 Q2 then the total shown should be SUM(YTD(2017 Q4)) * AVERAGE(YTD(2017 Q4 Rate)) + SUM(YTD(2018 Q2)) * AVERAGE(YTD(2018 Q2 Rate))

 

So in the table above this would essentially be a running sum from the beginning of time but applying the average rates for each year currency independently. I attempt to do just this but the context is modified by the outer SUM it seems.

Data Model:

Exchange Rate Model.pngData Model

PowerBI Template with Dummy Data and Model: Power BI Scaffold

 

I would greatly appreciate any help with this. I have complete control over the model, before this new exchange rate mechanism came in I was just using lookups and there was no requirement to have a relationship between the exchange table and anything else.

Best Regards,

 

Kieran

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 124 members 1,577 guests
Please welcome our newest community members: