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

I now have January working correctly with the following measures:

FX Account = 
CALCULATE (
    MAX ( CurrencyFX[rate] ),
    CurrencyFX[Type] = "Account",
    TREATAS (
        SUMMARIZE ( GLEntry, GLEntry[GLAccountNo-3], GLEntry[$Company] ),
        CurrencyFX[number],
        CurrencyFX[company]
    )
)
FX Monthly = 
CALCULATE (
    MAX ( CurrencyFX[rate] ),
    CurrencyFX[Type] = "Monthly",
    TREATAS (
        SUMMARIZE ( GLEntry, GLEntry[$Company], DimDate[MonthYear] ),
        CurrencyFX[company],
        CurrencyFX[month]
    )
)
FX Rate = 
COALESCE(
    [FX Account],
    [FX Monthly],
    1
)
Actual FX = 
SUMX ( DimCompany, [Actual] * [FX Rate] )
Actual Cumulative = 
VAR CurrentMonth = MAX('DimDate'[Date])
RETURN
    CALCULATE(
        [Actual FX],
        FILTER(
            ALL('DimDate'),
            'DimDate'[Date] <= CurrentMonth
        )
    )

 

However, February is using Januarys rates?

  • johnt75's avatar
    johnt75
    Super User
    4 months ago

    I think that

    FX Monthly =
VAR CurrentMonth =
    MAX ( DimDate[MonthYear] )
RETURN
    CALCULATE (
        MAX ( CurrencyFX[rate] ),
        CurrencyFX[Type] = "Monthly",
        TREATAS ( VALUES ( GLEntry[$Company] ), CurrencyFX[company] ),
        CurrencyFX[month] = CurrentMonth
    )

    will work as long as DimDate[MonthYear] is a date or datetime type. If its a text type, then you would need to use whichever column you have set as the sort by column.