Monday, February 18, 2019

Dax: currency conversion with date range

if you have daily exchange rate, the post at https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/ shows a nice solution, however if you are like me where exchange rates are only updated periodically hence come with a start and end date then you need a difference solution.

with a small change to the above, I came up with an alternative solution as below:




Sales (in reporting currency) := if(HASONEVALUE(ReportCurrency[ReportCurrency]),
SUMX(FactSales,FactSales[Sales]*CALCULATE(MIN(FactExchangeRate[Factor]),
FILTER(FactExchangeRate,
AND(FactExchangeRate[FromDate]<=FactSales[Date],FactExchangeRate[ToDate]> FactSales[Date])
)
)
)
)

YTD Sales (year to date) :=
SUMX(
CALCULATETABLE(FactSales,ALL(FactSales[Date]),DATESYTD(DATE[date]))
FactSales[Sales]*CALCULATE(MIN(FactExchangeRate[Factor]),
FILTER(FactExchangeRate,
AND(FactExchangeRate[FromDate]<=FactSales[Date],FactExchangeRate[ToDate]> FactSales[Date])
)
)


)


LTD Sales (life to date) :=


SUMX(
CALCULATETABLE(FactSales,ALL(FactSales[Date]),
FILTER(ALL(DATE),DATE[Date] <= MAX(DATE[date]))
)
FactSales[Sales]*CALCULATE(MIN(FactExchangeRate[Factor]),
FILTER(FactExchangeRate,
AND(FactExchangeRate[FromDate]<=FactSales[Date],FactExchangeRate[ToDate]> FactSales[Date])
)
)

)

The above formula are quite resource intensive though, hence if you are working on a very large dataset, it is probably better to have them pre-calculated