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.Incorrect result with simple operation – Consolidated Curr. Conv. with Calculation Groups
Initial Situation: A client has different companies and each one has a main currency. Revenues can be made in different currencies by each companies. One Revenue/Sale of one company is stored in two versions. One where the revenue is stored as the Company Currency and one where the revenue is stored as euro. The column RevenueVariant indicates which is which. Goal: The client wants a report where she/he needs to filter just one dimension (Reporting Currency) and can analyze all companies in this currency. The requirements are that the revenue with the RevenueVariant=“Company Currency” should not be converted if the selected reporting currency already equals the company currency. But if they differ then the revenue with the RevenueVariant = “EUR” should be used to convert the revenue to the selected reporting currency. An fx rate table is available and contains all fx rates based on EUR. So EUR->USD is 1.2 in this example. It is important to note that the fx rate with which the revenues are calculated and stored in the database (difference in “Company Currency” and “EUR” of RevenueVariant) have not to be equal to those fx rates in the fx rate table. Implementation: Because some measures in the tabular model require different currency conversion logic. I decided to use Calculation Groups and a Wrapper-Measure for my currency conversion. This way I can adjust the currency conversion on a per measure basis. Each currency conversion logic is in a separate calculation group attribute. I am using Power BI Desktop (Report Server Version January 2020) and Tabular compatibilityLevel = 1500. I have a live connection from Power BI Desktop to the tabular model. Problem: If I want to use the consolidated and converted revenue (which is displayed correctly) in another calculation, I get a very high number that is not explicable to me. Even if I just add the number 1 to the measure I get an incorrect result. All four operations (+,-,*,/) follow that behavior. Tabular Model: Source Tables: There are three tables as data source. As mentioned before regarding the fx rates: if you calculate the fx rate of the revenue of company BBB manually you will get 13/10 = 1,3 and it differs from 1,2 from the fx rates table. But this is okay, as mentioned above. The fx rates table and the fx rates of the fact table do not have to be the same. Relationships: There is only one relationship. T_CM_DIM_Rep_Cur is connected to the FX Rates table (T_CM_FA Currency) so that if a user filters on reporting currency the correct fx rate gets selected. Calculation Group Attributes: CG_Attribute_1: SUMX ( 'T_CM_CG_Fact_Table', -- If the selected reporting currency equals the company currency no currency conversion is needed -- and the Revenue with RevenueVariant "Company Currency" is used in the current row of SUMX. IF ( 'T_CM_CG_Fact_Table'[CompanyCurrency] = SELECTEDVALUE ( 'T_CM_DIM_Rep_Cur'[Currency] ) , CALCULATE ( SELECTEDMEASURE (), 'T_CM_CG_Fact_Table'[RevenueVariant] = "Company Currency" ), CALCULATE ( SELECTEDMEASURE () --If the following statement is uncommented the addition by 1 works in the [REVENUE | CG_Attribute_1 + 1] measure: --/ SWITCH ( "EUR", "EUR", 1,"XYZ", SUM ( 'T_CM_FA CURRENCY'[RATE] ), 1 ) , 'T_CM_CG_Fact_Table'[RevenueVariant] = "EUR" ) * MAX ( 'T_CM_FA Currency'[RATE] ) ) ) CG_Attribute_2: SUMX ( 'T_CM_CG_Fact_Table', -- If the selected reporting currency equals the company currency no currency conversion is needed -- and the Revenue with RevenueVariant "Company Currency" is used in the current row of SUMX. IF ( 'T_CM_CG_Fact_Table'[CompanyCurrency] = SELECTEDVALUE ( 'T_CM_DIM_Rep_Cur'[Currency] ) , CALCULATE ( SELECTEDMEASURE (), 'T_CM_CG_Fact_Table'[RevenueVariant] = "Company Currency" ), CALCULATE ( SELECTEDMEASURE () --If the following statement is commented the addition by 1 does not work in the [REVENUE | CG_Attribute_2 + 1] measure: / SWITCH ( "EUR", "EUR", 1,"XYZ", SUM ( 'T_CM_FA CURRENCY'[RATE] ), 1 ) , 'T_CM_CG_Fact_Table'[RevenueVariant] = "EUR" ) * MAX ( 'T_CM_FA Currency'[RATE] ) ) ) Measures: [REVENUE_hidden]= SUM( 'T_CM_CG_Fact_Table'[Revenue] ) [REVENUE | CG_Attribute_1]= CALCULATE( [REVENUE_hidden] , '_CalcGroup'[Attribute] = "CG_Attribute_1" ) [REVENUE | CG_Attribute_2]= CALCULATE( [REVENUE_hidden] , '_CalcGroup'[Attribute] = "CG_Attribute_2" ) [REVENUE | CG_Attribute_1 + 1]= [REVENUE | CG_Attribute_1] +1 [REVENUE | CG_Attribute_2 + 1]= [REVENUE | CG_Attribute_2] + 1 Problematic result: Questions: Q1: Does anyone know why I get this high number after the simple addition? Q2: Why does the Measure [REVENUE + 1 | CG_Attribute_2] work while the one with CG_Attribute_1 does not? The only difference is that in CG_Attribute_2 the seemingly irrelevant code: "/ SWITCH ( "EUR", "EUR", 1,"XYZ", SUM ( 'T_CM_FA CURRENCY'[RATE] ), 1 )" is added. I found this solution only by trial and error. I have to use the FX Rate Table in the Switch statement otherwise it gives the same incorrect result as CG_Attribute_1. Also the Operator SUM in the Switch statement does not matter of course. This part of the switch statement should never be executed anyway because of the static "EUR","EUR",1 at the beginning of the switch statement. Q3: When I create a report level measure in Power BI Desktop and define the measure exactly like [REVENUE | CG_Attribute_1 + 1] I get the correct result (last column in the previous screenshot). Does anyone know why? I really appreciate any input! Thank you! -David
