Forum Discussion
Solved
Currency FX Rates
Hi everyone,
I have a pretty challenging DAX task (In my opinion, hopefully someone tells me it's easy!).
I am currently putting together a balance sheet and I have the following Currenc...
I think the only change you need to make is to [Actual FX]
Actual FX = SUMX ( DimCompany, [Actual] * [FX Rate] )That guarantees that a company is always selected, even when calculating totals.
If performance is an issue then you could also consider making the below changes.
FX Account = CALCULATE ( MAX ( CurrencyFX[rate] ), TREATAS ( SUMMARIZE ( GLEntry, GLEntry[GLAccountNo-3], GLEntry[$Company] ), CurrencyFX[number], CurrencyFX[company] ) ) FX Monthly = CALCULATE ( MAX ( CurrencyFX[rate] ), TREATAS ( SUMMARIZE ( GLEntry, GLEntry[$Company], DimDate[MonthYear] ), CurrencyFX[company], CurrencyFX[month] ) )Using a single TREATAS / SUMMARIZE rather than multiple TREATAS / VALUES should speed it up a bit.
I think the only change you need to make is to [Actual FX]
Actual FX =
SUMX ( DimCompany, [Actual] * [FX Rate] )
That guarantees that a company is always selected, even when calculating totals.
If performance is an issue then you could also consider making the below changes.
FX Account =
CALCULATE (
MAX ( CurrencyFX[rate] ),
TREATAS (
SUMMARIZE ( GLEntry, GLEntry[GLAccountNo-3], GLEntry[$Company] ),
CurrencyFX[number],
CurrencyFX[company]
)
)
FX Monthly =
CALCULATE (
MAX ( CurrencyFX[rate] ),
TREATAS (
SUMMARIZE ( GLEntry, GLEntry[$Company], DimDate[MonthYear] ),
CurrencyFX[company],
CurrencyFX[month]
)
)
Using a single TREATAS / SUMMARIZE rather than multiple TREATAS / VALUES should speed it up a bit.
