topic Re: Currency FX Rates in DAX Commands and Tips

Currency FX Rates

wardy912 — Wed, 01 Apr 2026 08:25:17 GMT

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!

Re: Currency FX Rates

johnt75 — Wed, 01 Apr 2026 09:03:59 GMT

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.

Re: Currency FX Rates

wardy912 — Wed, 01 Apr 2026 09:27:24 GMT

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.

Re: Currency FX Rates

wardy912 — Wed, 01 Apr 2026 13:58:02 GMT

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?

Re: Currency FX Rates

johnt75 — Wed, 01 Apr 2026 14:16:38 GMT

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.

Re: Currency FX Rates

wardy912 — Wed, 01 Apr 2026 14:57:15 GMT

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