Forum Discussion

wardy912's avatar
wardy912
Super User
4 months ago
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...
currency conversion
DAX
  • johnt75's avatar
    johnt75
    4 months ago

    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.

wardy912's avatar
wardy912
Super User
4 months ago

Got this working now.

Found that it was only applying the monthly rate to GLEntry rows that were in that month, defaulting to 1 if there were no entries.

I had to adjust the monthly rate:

FX Monthly = 
CALCULATE (
    MAX ( CurrencyFX[rate] ),
    CurrencyFX[Type] = "Monthly",
    TREATAS (
        SUMMARIZE ( DimDate, DimDate[MonthYear] ),
        CurrencyFX[month]
    ),
    TREATAS (
        SUMMARIZE ( DimCompany, DimCompany[$Company] ),
        CurrencyFX[company]
    )
)


I then had to calculate the cumulative measure before applying the rate

Actual Cumulative FX = 
VAR CurrentMonth = MAX('DimDate'[Date])
RETURN
    SUMX(
        DimCompany,
        CALCULATE(
            [Actual],
            FILTER(
                ALL('DimDate'),
                'DimDate'[Date] <= CurrentMonth
            )
        )
        * [FX Rate]
    )


Thanks for your help johnt75