cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonSon2015
Frequent Visitor

Applying exchange rate to every row

Hello,
I'm currently setting up a data model for monthly reporting where I need to convert the figures from foreign currency to EUR. However, it works fine on total level but does not show the correct figures on deepest level.

Setup

:2021-09-02_15h47_01.png
Sales_Cond = fact table
Calendar = calendar
Exchange_Rate = table with dates and exchange rates from foreign currency to EUR
Currency = distinct currencies (USD, GBP, DKK, etc.)

Filter in output: e.g. Month = 6; Year = 2021; Country = Denmark

Formula:
Turnover YTD current year (EUR):=

SUMX('Currency';

DIVIDE(

CALCULATE(SUM(Sales_Cond[Turnover value (LC)]);Sales_Cond[Customer]="ZLBO";DATESYTD('Calendar'[Date];"12/31"))*-1;

[Exchange Rate YTD CY]

)

)

 

Exchange Rate YTD CY:=SELECTEDVALUE(Exchange_Rate_MTD_YTD[YTD])


The total is fine on YTD and country level. However, if I drag in a lower level like products, the total is still fine but on product level it's in most cases wrong (either empty or wrong value).

 

 

Thanks a lot in advance!

1 ACCEPTED SOLUTION
RonSon2015
Frequent Visitor

I've solved the problem. When switching the filter direction for currency and fact table (sales cond) from both ways to currency to fact table it works.

View solution in original post

1 REPLY 1
RonSon2015
Frequent Visitor

I've solved the problem. When switching the filter direction for currency and fact table (sales cond) from both ways to currency to fact table it works.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors