DAX currency translation
Hi, I would be really greatful is someone could assist. I'm very new to power BI and after searching a lot I'm still struggling for a solution here that suits my setup. My data is all in one currency & I want to convert it into different versions of dollar rates. The DAX query i pulled together worked okish when I had the same rate for each period (I dont have dates in the data, just numbered financial periods 1 to 12). However some of my currency scenarios have a different rate per period and they pull in blank, so I need help changing this query to take into account the rates at different periods. The second issue is in my value column I'm converting there is also volume that I do no want to convert and keep at the same value, this pulls from my P&L column in the data sheet Current DAX formula: FX adjusted value = var value_ = CALCULATE(DATASHEETS[Sum of Value]) var ExchangeRate_ = selectedvalue('Currency'[Currency rate]) return value_ * ExchangeRate_ Data sheet: Currency sheet: Also including relationships if this helps - the Period reference is what links them Thanks for any assistance
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. 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: 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
