Applying exchange rate to every row

[RonSon2015]

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

:
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!

[RonSon2015 · Accepted solution]

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.