Hello forum members,
Here is the details for the report.
I have accessed Yahoo finance for the historical exchange and kept in CSV file.
– Currency format
Created currency format referred EDNA video.
*To make use of Date Period I have disconnected “Date” and “ExRate” table.
without connecting these tables, Dax Lookupvalue have done very good job to achieve currency conversion
*Need data clean up as I have notice some dates are missing in Delivery date, confirmed load date and Est Load date. So I have created some logic to use for currency exchange date.
*As this is my first challenge for the currency conversion, I wanted model as simple as possible.
So I have selected single currency model (USD) for the fact table this time.
*To bring USD exchange rate to the fact table I have merged two tables using left outer join by Date & currency.
I have created three exchange columns for Purchase , sales and Haulage.
TradingF[SalesEX]*TradingF[Weight]*TradingF[Unit Sales Price])
var CurrentDate= MAX(TradingF[ExDate])
var currencyselected= SELECTEDVALUE('Format'[Ticker],"USD")
var CurrentExrate= LOOKUPVALUE(ExRate[Value],ExRate[Date],CurrentDate,
var converttedsales= [TotalSales]*1/CurrentExrate
var sales= [ConvertedSales]
var formatting= LOOKUPVALUE('Format'[Format],
var AddonCurrency= SELECTEDVALUE('Format'[Ticker],"USD")
var result=CONCATENATE(AddonCurrency&" ",FORMAT([ConvertedSales],formatting))
var clearblank= IF(ISBLANK([ConvertedSales]),BLANK(),result)
(I have used Z score rather than rankx or topn this time and found this is easy and useful)
Sales Z score =
var zscore= DIVIDE([ConvertedSales]-allave,allstd,0)
SalesOutlier = IF([Sales Z score]>3 || [Sales Z score]<-3,1,0)
As this is my first time to deal with currency conversion, eDNA is having plenty of good videos so I have learned a lot.
Also I really appreciate for all the members who are supporting each other.
This is a great community.
Thank you so much eDNA for giving me a learning opportunity.