Currency FX Rates

[wardy912]

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!

[wardy912]

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 

[wardy912]

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]

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.

[johnt75 · Accepted solution]

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]

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.