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 CurrencyFX table:

company starting_date month rate type number
Company_1 01/04/2026 Apr-26 0.01401 Monthly 0
Company_1 01/03/2026 Mar-26 0.014146 Monthly 0
Company_1 28/02/2026 Feb-26 0.014184 Monthly 0
Company_1 31/01/2026 Jan-26 0.014286 Monthly 0
Company_2 01/04/2026 Apr-26 0.864304 Monthly 0
Company_2 01/03/2026 Mar-26 0.871916 Monthly 0
Company_2 28/02/2026 Feb-26 0.878272 Monthly 0
Company_2 31/01/2026 Jan-26 0.867002 Monthly 0
Company_3 01/04/2026 Apr-26 0.008072 Monthly 0
Company_3 01/03/2026 Mar-26 0.008126 Monthly 0
Company_3 28/02/2026 Feb-26 0.008169 Monthly 0
Company_3 31/01/2026 Jan-26 0.00793 Monthly 0
Company_4 01/04/2026 Apr-26 0.864304 Monthly 0
Company_4 01/03/2026 Mar-26 0.871916 Monthly 0
Company_4 28/02/2026 Feb-26 0.878272 Monthly 0
Company_4 31/01/2026 Jan-26 0.882768 Monthly 0
Company_2 01/01/2026 Jan-26 0.903222 Account 20101
Company_4 01/01/2026 Jan-26 0.837333 Account 20101
Company_3 01/01/2026 Jan-26 0.008748 Account 20101
Company_1 01/01/2026 Jan-26 0.014078 Account 20101
Company_2 01/01/2026 Jan-26 0.903222 Account 20102
Company_4 01/01/2026 Jan-26 0.837333 Account 20102
Company_3 01/01/2026 Jan-26 0.008748 Account 20102
Company_1 01/01/2026 Jan-26 0.014078 Account 20102
Company_2 01/01/2026 Jan-26 0.903222 Account 20103
Company_4 01/01/2026 Jan-26 0.837333 Account 20103
Company_3 01/01/2026 Jan-26 0.008748 Account 20103
Company_1 01/01/2026 Jan-26 0.014078 Account 20103
Company_2 01/01/2026 Jan-26 0.903222 Account 20201
Company_4 01/01/2026 Jan-26 0.837333 Account 20201
Company_3 01/01/2026 Jan-26 0.008748 Account 20201
Company_1 01/01/2026 Jan-26 0.014078 Account 20201
Company_2 01/01/2026 Jan-26 0.903222 Account 20202
Company_4 01/01/2026 Jan-26 0.837333 Account 20202
Company_3 01/01/2026 Jan-26 0.008748 Account 20202
Company_1 01/01/2026 Jan-26 0.014078 Account 20202
Company_2 01/01/2026 Jan-26 0.830597 Account 20301
Company_4 01/01/2026 Jan-26 0.872245 Account 20301
Company_3 01/01/2026 Jan-26 0.008247 Account 20301
Company_1 01/01/2026 Jan-26 0.014376 Account 20301
Company_2 01/01/2026 Jan-26 0.830597 Account 20302
Company_4 01/01/2026 Jan-26 0.872245 Account 20302
Company_3 01/01/2026 Jan-26 0.008247 Account 20302
Company_1 01/01/2026 Jan-26 0.014376 Account 20302
Company_2 01/01/2026 Jan-26 0.830597 Account 20303
Company_4 01/01/2026 Jan-26 0.872245 Account 20303
Company_3 01/01/2026 Jan-26 0.008247 Account 20303
Company_1 01/01/2026 Jan-26 0.014376 Account 20303
Company_5 01/01/2026 Jan-26 1 Fixed 0
Company_6 01/01/2026 Jan-26 1 Fixed 0
Company_7 01/01/2026 Jan-26 1 Fixed 0
Company_8 01/01/2026 Jan-26 1 Fixed 0
Company_9 01/01/2026 Jan-26 1 Fixed 0
Company_10 01/01/2026 Jan-26 1 Fixed 0
Company_11 01/01/2026 Jan-26 1 Fixed 0
Company_12 01/01/2026 Jan-26 1 Fixed 0

 

My Base measures are:

Actual = SUM(GLEntry[Amount])

Actual Cumulative = 
VAR CurrentMonth = MAX('DimDate'[Date])
RETURN
    CALCULATE(
        [Actual],
        FILTER(
            ALL('DimDate'),
            'DimDate'[Date] <= CurrentMonth
        )
    )

 

I have the following requirements:


If CurrencyFX[Type] = "Monthly", multiply Actual by the current months rate for that company. This is a balance sheet so I will be using a cumulative figure. In January multiply Januarys Actual by January rate. In February, multiply January + February Actual by Februarys rate, etc.

If CurrencyFX[Type] = "Account", when GLEntry[GLAccountNo] = CurrencyFX[number] for the specified company, multiply Actual by that rate in every month.

If CurrencyFX[Type] = "Fixed" always multiply by that rate (Intention is to give Actual without conversion)

 

I have the following DAX measures that work only when a company is selected. I need to be able to select all companies to provide group numbers. I added all companys to the fx table and added a relationship to DimCompany to see if this would fix it but it didn't.

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

  

 

I hope I have explained that well enough! Looking forward to your inputs.
I can't post GLEntry data for obvious reasons.

Thanks!

 

 

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.

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    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

      Thanks johnt75 !

       

       That's definitely fixed the company issue!

      Also, thanks for the performance tip.

       

      I'll get some testing done as a couple of the numbers are still wrong and get back to you asap.

       

  • 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.

  • 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