电力BI汇率计算

Question

我是Power BI桌面的新手。我们有一个包含RateDate、orderAmount和交易货币的销售交易表

​

​

此外，我们还有一个包含BaseCurrency(标准货币，即美元、欧元和印度卢比)、目标货币、生效日期和汇率的汇率表。

​

​

为了进行分析，我们需要根据汇率日期-生效日期动态映射将Salesamount转换为任何基础货币，即美元/INR/欧元。我怎样才能做到这一点呢？

我试着跟踪DAX代码，

AmountUSD = SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date],Fact_SalesOrder[RateDate],CurrencyRates[Base Currency],"USD",CurrencyRates[Target Currency],Fact_SalesOrder[Currency]))

但它会产生“无穷大”的值。

​

​

Answer 1

终于明白了:)

只需要2个措施: SalesAmount的销售和选定的货币选择。

Selected Currency = VALUES('ReportingCurrencies'[Currency])

和

SalesAmount = SWITCH(TRUE(),
[Selected Currency] = "EUR",SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date].[Date],Fact_SalesOrder[RateDate].[Date],CurrencyRates[Base Currency],"EUR",CurrencyRates[Target Currency],Fact_SalesOrder[Currency],1)),
[Selected Currency] = "USD",SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date].[Date],Fact_SalesOrder[RateDate].[Date],CurrencyRates[Base Currency],"USD",CurrencyRates[Target Currency],Fact_SalesOrder[Currency],1)),
[Selected Currency] = "INR",SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date].[Date],Fact_SalesOrder[RateDate].[Date],CurrencyRates[Base Currency],"INR",CurrencyRates[Target Currency],Fact_SalesOrder[Currency],1)))