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!
Multiple-Multiple Currency Conversion
Hi all, I have the below measure to convert my Transactions (or Sales) data from multiple currencies (GBP,USD,ZAR) to multiple currencies (GBP,USD,ZAR,KES,INR,NGN). My code and model is shown below, however, I keep getting the error that more than one currency has been selected, despite having selected just one in the screenshot of my report. Sales (Monthly) = IF ( HASONEVALUE('Target Currency'[CurrencyCode]), VAR AggregatedSalesInCurrency = ADDCOLUMNS( SUMMARIZE( Transactions, 'Date'[Calendar Month Year], --maybe just use Month 'Source Currency'[TxnCurr] ), "@SalesAmount",[Sales (internal)], "@Rate",CALCULATE(SELECTEDVALUE('Exchange Rates - Monthly'[Rate])) ) VAR Result = SUMX( AggregatedSalesInCurrency, IF( NOT (ISBLANK([@Rate])), [@SalesAmount]*[@Rate], ERROR("Missing conversion rate") ) ) RETURN Result ) I think the issue might be pertaining to the fact that I am using one exchange rate for each month, but I have formatted the Exchange rates - Monthly[Date] to only be yyyy/mm, so I'm not quite sure what the issue is. Any help would be very much appreciated!
How do I Currency Convert All Values to Chosen Currency in Drop Down?
Hi all, I need a bit of help referencing a currency table and using the exchange rate provided in that table, against the relevant date, to convert the value to the currency the user has selected in a drop down on the report. When this currency from the drop down is selected I need all the values in my table ('table A') changed using this exchange rate. The maybe tricky part is that for each row in Table A (each Asset listed in the row) which contains the Asset Value, displays the native currency value as standard as per in the model view the currency column in Table A. So there are several different native currencies listed/referenced, so the native currency against the asset needs to be looked up, this is then used in the currency conversion rates table against the date against for the converstion and used to adjust the value. The added problem is that ECB only give a rate to Euros. So in all cased the amount will need to be converted to Euros and then out to the native currency again for situations where the asset's native currency is DKK , and the user has selected something like AUD for the value to be converted to. Example Data subset from EBC in past 90 Days <Cube time="2022-06-30"> <Cube currency="DKK" rate="7.4392"/> <Cube currency="AUD" rate="1.5099"/> </Cube> [EUR -> EUR] * [EUR -> DKK] [1/1.0000] * [7.4392] = 7.4392 (this one is simple as the rate matches that from the ECB extraction) [AUD -> EUR] * [EUR -> DKK] [1/1.5099] * [7.4392] = 4.9269 I've attached a sample file with this dummy data in for reference. There is an API connection to ECB to get the currency conversion rates for relevant currencies I need. It's a bit meaty this one but any help at all I'd be grateful for. Sample File
Currency Conversion
I have a fact table of Sales from three facilities. Two work in Euro and one in GBP. I have a full Date table with conversion for EURO, GBP and USD. This works, line by line, but the total isn't correct. It appears to go askew whenever the Conversion rate changes in the Dates table. //note, we use on conversion rate per month.// I'm using a LOOKUPVALUE measure to convert all Euro to GBP: //fyi GBP conversion = 1// Daily Sales GBP = SUMX('Daily Sales','Daily Sales'[LC_AMOUNT]/ LOOKUPVALUE('Currency Rates'[Exchange Rate], 'Currency Rates'[Date], 'Daily Sales'[ACTION_DATE], 'Currency Rates'[Foreign Currency], 'Daily Sales'[CURRENCY])) I then us the LOOKUPVALUE measure again to convert the now all GBP values to whatever I decide to choose on my "Ticker". Current Date = MAX(Dates[Date] ) Currency Selected = SELECTEDVALUE( Currency_Format[Ticker],"GBP") Converted Sales = VAR _Date = [Current Date] VAR _CurrencySelected = [Currency Selected] VAR _Sales = [Daily Sales GBP] VAR _Rate = LOOKUPVALUE( 'Currency Rates'[Exchange Rate], 'Currency Rates'[Foreign Currency], _CurrencySelected, 'Currency Rates'[Date],_Date) VAR _ConvertedMeasure = _Sales*_Rate RETURN _ConvertedMeasure
Calculate net of VAT and un GBP with DAX
Dear community, I am looking for some help to write a DAX formula that would return the sales amount net of VAT and in GBP based on two columns. My current data model is composed of the following tables: Actual { Property, Date, Currency, Food Sales, Beverage Sales} VAT { Date, VAT, Item } < Items = {Food Sales, Beverage Sales} Currency { Currency, Rate, Date } < Currency = { EUR/GBP } Calendar { Date, Month ... etc } < As Date Table BusinessUnits { Property Name, Code, etc.... } Note that the VAT and currency have different values over time. I have the following relationships: Actual.Property 1 to many relationships with BusinessUnits.PropertyName Actual.Date 1 to many relationships with Calendar.Date VAT.Date 1 to many relationships with Calendar.Date Currency.Date 1 to many relationships with Calendar.Date This is my current formula that calculates my Net Food Sales. I'm not sure how to write the second part that would transform my EUR revenues into GBP. I'm wondering if I should use the VAR function, any recommendations? Net Food Sales:=SUMX( Actual, DIVIDE( Actual[Food Sales] , (1 + IFERROR( LOOKUPVALUE(VAT[VAT], VAT[Item] , "Food Sales", VAT[Date], Actual[Date]) , 0.2 )) )) Also, would you recommend computing these calculations in PowerQuery or keep them in DAX? Many thanks for your help. Best,
Divide by Constant Currency
Hello I am trying to optimice the following Dax measure: SUMX ( FILTER(Complete_DATA,Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1])), DIVIDE ( Totales[Gross Sales], SUMX( FILTER( FILTER(FX,FX[Version]=Forex[Selected Currency]), FX[Month]=Complete_DATA[Month]), FX[EUR to US] ) ) ) I have 2 slicers, one to select an scenario (From the Complete_DATA table), and another to select the currency. The idea is that the [Gross Sales] be divided by the corresponding currency (according to the scenario selected) and month independent of the "Date" as I could need 2020 sales in 2019 Exchange Rates. FX table has the following structure: Date Month Version EUR to US 01/01/2020 January LC$ 1 01/01/2020 January USD 2020 1.02 01/01/2019 January USD 2019 1.05 The previous DAX formula works, but is incredibly inefficient as it first goes to each of the Complete_DATA registry, divides the corresponding currency and then summarizes everything. With small data it doesn't matter but right now I have query updates of +3 minutes. I have tried the following with no success as Exchange is not calculating correctly: Gross Sales 1 = var Sales = CALCULATE( Totales[Gross Sales], Complete_Data[Version] in VALUES('Scenario 1'[Scenario 1]) ) var Exchange = CALCULATE( [TRM], FX[Version] in VALUES(Forex[Currency]), FX[Month] = SELECTEDVALUE(Calendario[Month]) ) return DIVIDE(Sales,Exchange) Any suggestions?Convert Measure to calculation group
Hi all, I´ve this measures that convert sales currency (multiple currencies) to USD, EUR and GBP currency selected in a report slicer: CreationForex = IF ( ISFILTERED ( CreationForex[CurrencyTo] ), VAR ForexRate = CALCULATE ( FIRSTNONBLANK ( CreationForex[Rate], 1 ), FILTER ( CreationForex, CreationForex[Currency] = SELECTEDVALUE ( Sales[SaleCurrency] ) && CreationForex[CurrencyTo] = SELECTEDVALUE ( CreationForex[CurrencyTo] ) ) ) VAR CurrencyFormat = SELECTEDVALUE ( CreationForex[CurrencyCharacter] ) VAR NetSalePrice = [SumNetSalePrice] VAR Result = CONCATENATE ( CurrencyFormat, FORMAT ( DIVIDE ( NetSalePrice, ForexRate, 0 ), "0.00" ) ) RETURN Result, [SumNetSalePrice] ) I have [SumNetSalePrice] = SUM (NetSalePrice) and wanted to create more measures, all of them sumatories of diferents columns. Then using them in a table like: And change the value of this measures using the slicer. Now is working correctly but of course only with the measure hardcoded in the CreationForex measure. I´ve tryied to convert the CreationForex to a calculation group: CreationForex = IF ( ISFILTERED ( CreationForex[CurrencyTo] ), VAR ForexRate = CALCULATE ( FIRSTNONBLANK ( CreationForex[Rate], 1 ), FILTER ( CreationForex, CreationForex[Currency] = SELECTEDVALUE ( Bookings[SaleCurrency] ) && CreationForex[CurrencyTo] = SELECTEDVALUE ( CreationForex[CurrencyTo] ) ) ) VAR CurrencyFormat = SELECTEDVALUE ( CreationForex[CurrencyCharacter] ) VAR NetSalePrice = SELECTEDMEASURE () VAR Result = CONCATENATE ( CurrencyFormat, FORMAT ( DIVIDE ( NetSalePrice, ForexRate, 0 ), "0.00" ) ) RETURN Result, SELECTEDMEASURE () ) I get an error from Tabular editor and Power Bi, I'm facing this wrongly? Any idea how can be this achieve? I'll try to test calculation groups with easiest examples, but if any master user 😉 can tell me id this kind of things can be done, I´ll own him/her a drink! Thanks in advance for your time.
