Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.