Hi Julian,
This is just an addition concerning the question whether the can by a performant completely measure-based approach. So, no action required.
As we have been going back and forth to find on a hands on solution it was still unclear if we can have a sufficiently performant approach based on measures alone. I came across “semi-additive” measures in the “DAX Patterns” book and this gives part of the answer how this is feasible.
To complete the topic and to provide a comprehensive description of the approach I will give a full description below. The approach with pre-calculations of exchange rates and amounts in calculated columns is still completely OK. This consumes space for calculated columns, but at an affordable cost for our discussed scenario.
Still, by describing the approach we can see better what is going on. And it is simply a community contribution. So, let’s start:
Fully Measure Based Semi-Additive Approach for Currency Conversion:
Data Model:
We generalize and simplify the model to the essence of the problem. We have tables as follows:
- Entity table: a table with all entities containing some category information.
- Transactions: containing the entity ID, the transaction date, and the amount
- Currencies: containing all currencies and the display currency like in our case
- Exchange rates: contains the exchange rates per currency and a date. The only additional requirement is that all dates in the interval are present for every currency and the base currency (in our case “EUR”) is present for every day with exchange rate 1. This can be easily achieved come from any base exchange rate table by filling dates with missing exchange rates with last known exchange rate for this currency and appending the exchange rate 1 for the base currency for every date
- Date table: as used
The rest of the original model, like the sales recognition with value zero is not relevant for the performance. In the simulation we have
- 25 currencies
- 2000 entities
- 5000 transactions
A word of warning as all figures are random numbers, an update of the model values will change all random numbers and if we had e.g. 5 transactions before there might be non as the number of transactions per entity is random, too.
The model relationships are as per screenshot below. The entity table and the date table filter the fact table “Transactions”. The only difference to our work is that the transaction table has already the original currency as column. The element we have been missing is that there is an exchange rate ID which is composite key of date and currency as we should have exactly one exchange rate per currency and day. With that the transaction table will have an exchange rate ID for the original currency for the valuation date. This is how we define the relationship between the exchange rate table and the transaction table.
So, this is a standard star schema for the problem:
Now measures are defined as follows:
OriginalExchangeRate =
CALCULATE (
SUM ( ExchangeRates[OBS_VALUE] ),
CROSSFILTER ( 'Transaction'[OriginalCurrencyExchangeID], ExchangeRates[ExchangeRateID], BOTH )
)
Here, we use the fact that the exchange rate ID is unique and with the existing relationship between transaction table and exchange rate table we get exactly one record from the exchange rate table by using CROSSFILTER. This gives the exchange rate. This uses the existing relationship and is supposed to be quicker than using LASTNONBLANK as discussed before.
EuroAmount =
SUMX ( 'Transaction', 'Transaction'[Amount] / [OriginalExchangeRate] )
with the above exchange rate the Euro amount as base value can be calculated.
TargetExchangRate =
VAR thisTransactionDate =
FIRSTNONBLANK ( 'Transaction'[TransactionDate], 0 )
VAR exchangeRateID =
COMBINEVALUES (
"-",
FORMAT ( thisTransactionDate, "yyyy-MM-dd" ),
[Selected Currency]
)
VAR exchangeRate =
LOOKUPVALUE (
ExchangeRates[OBS_VALUE],
ExchangeRates[ExchangeRateID], exchangeRateID
)
RETURN
exchange Rate
The target exchange rate is depending on the selected currency.
ValuationLastDateDAXPattern =
VAR maxDate =
MAX ( DateTable[Date] )
VAR maxValuationDates =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'Transaction', Entity[Entity] ),
"MaxValuationDate", CALCULATE ( MAX ( 'Transaction'[TransactionDate] ) )
),
DateTable[Date] <= maxDate
)
VAR maxValuationDatesWithLineage =
TREATAS ( maxValuationDates, Entity[Entity], DateTable[Date] )
VAR result =
CALCULATE ( [TargetAmount], maxValuationDatesWithLineage )
RETURN
Result
Above is now the part where the “DAX Patterns” knowledge is used: as before the max date of the date filter is used (max date in quarter etc., …). SUMMIRIZE gets all entities and relevant transaction dates. The TREATAS substitutes the SUMMARIZE information in the filter tables Entity and ‘DateTable’. Now the target amount (the amount converted into the selected currency) is calculated with the substituted filter.
As you can see in the relevant visual this is working and quicker than our hands-on approach with measures only.
We now have a tradeoff for the calculated column approach. The exchange rate conversion is precalculated, but the transaction table size is multiplied by the number of currencies (25 in the specific case).
The measure for the calculated column approach is now adjusted to match the DAX Pattern approach above:
ValuationLastDateDAXPatternCC =
VAR maxDate =
MAX ( DateTable[Date] )
VAR maxValuationDates =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'TransactionsExtended', Entity[Entity] ),
"MaxValuationDate", CALCULATE ( MAX ( 'TransactionsExtended'[TransactionDate] ) )
),
DateTable[Date] <= maxDate
)
VAR maxValuationDatesWithLineage =
TREATAS ( maxValuationDates, Entity[Entity], DateTable[Date] )
VAR result =
CALCULATE ( [TargetAmountCC], maxValuationDatesWithLineage )
RETURN
result
Now, we the performance analyzer we see that the measure only approach takes about 600 to 700 ms for a currency change, the calculated column approach 1100 – 1200 ms.
Without proof I would say that there is a lower threshold for the number of currencies where the calculated column approach is quicker. So, it is a matter of projection:
- If the number of currencies stays (in our case) low and the number transactions only increases moderately the calculated column approach might be quicker
- The fully measure based approach is more scalable, more currencies and more transactions will presumably hurt the performance less.
As I said, no rigorous proof, but is sheds some light on the situation. What have we won:
- The data model for this kind of pattern is clearer.
- The DAX pattern approach from the book is applied and gives better performance.
The pbi file: CurencyConversionPerformance.pbix
OK, this is where I stand in terms of understanding. As I said no action to be taken. It is just the clarification for the record.
Best regards
Christian
@scee07 Happy New Year, and thank you greatly for the above response at the end of last year. I was just about to take vacation so have not until now been able to try apply it!
I think it might be the right solution - or certainly nearly there. But I am struggling to apply the first section creating the calendar. I have a slicer linked to my built in date table, so am wondering do I need to bother at all with assigning the min and max date selections
I thought it might be a good idea to send a link to my pbix here. - unable to upload on the forum.
It might help if I recap a little to make sure the solution you provided would work or if there is something missing, but also to illustrate a bit better what I was describing for others in a similar situation,
What I have on my report canvas currently is the following:
You will see the date picker already at the top (i hide the start date so user can't change). It's essentially linked to the [Date] of DateTable that I create in PQ. This provides the window of time to be viewed in the bar chart below it but also filters out any dates from other tables so data is not involved in the calculations and can be filtered out.
In the attachment pbix, there are four tables in the model at work:
1. DateTable - as described above
2. Asset Transaction - this is the acquisition value of the asset so when a 'purchase' it contains the very first record of its value. If there is a stamp saying 'sale', it marks the final year you can count the last valuation of the asset. Values for the asset should not run forward of this date.
| Asset Reference | Transaction Type | Transaction Date | Net Transaction Price |
| AAA100 | Purchase | 12 July 2018 | 5175000 |
| AAA103 | Purchase | 22 November 2013 | 37400000 |
| AAA107 | Purchase | 20 May 2011 | 8750000 |
| AAA130 | Purchase | 17 December 2002 | 3800000 |
| AAA130 | Sale | 19 October 2021 | 6000000 |
| AAA107 | Sale | 10 March 2021 | 10000000 |
3. Asset Valuation
This lists consequent reviews of the asset value through time. These are irregular times. Some years are empty.
Even if there is a year missed in the Valuation Date column, the DAX calculation should carry forward the last known valuation into any gap years and count it as the value for it.
| Asset Reference | Valuation Date | Asset Value |
| AAA100 | 30-Sep-18 | 5400000 |
| AAA100 | 31-Dec-18 | 5400000 |
| AAA100 | 31-Dec-19 | 5400000 |
| AAA100 | 31-Mar-20 | 5250000 |
| AAA100 | 30-Jun-19 | 5400000 |
| AAA100 | 31-Mar-19 | 5400000 |
| AAA100 | 30-Sep-19 | 5400000 |
| AAA103 | 30-Sep-18 | 43900000 |
| AAA103 | 31-Dec-18 | 43900000 |
| AAA103 | 31-Dec-19 | 43900000 |
| AAA103 | 31-Mar-20 | 43900000 |
| AAA103 | 30-Jun-19 | 43900000 |
4. Building
This lists the asset references and provides attributes for them that can be used in the bar chart to split the total values by categories.
| Asset Reference | Risk Profile |
| AAA100 | A |
| AAA103 | D |
| AAA107 | A |
| AAA130 | D |
It should be worth noting then that is a 'purchase' of an asset has been made in Asset Transaction table but there is no record of a review of the valuation in the Asset Valuation table, then the Asset Transaction value should be used. That's an extra point that I came to realise was absent before but needs to be part of the calculation.
As you can see in the bar chart visualisation, there are some years with complete gaps and that's because it is not currently carrying over the last known value from the Asset Valuation table for the asset into further years. I think my DAX code is inaccurate:
ValueLastDate = Calculate(SUM('Asset Valuation'[Asset Value]), LASTDATE('Asset Valuation'[Valuation Date]))
Does your solution still work in this scenario? I was lost with the DAX created table you provided. I'm not sure if I need this, but was also confused about the reference to Years[Year] and whether I need both snippets of your code b and c?
Sorry for the confusion. This level of complexity is a hard one for me to grasp on this one! I am still open to the idea of adding columns to existing tables if that is something that would break down the complexity and work with the dynamic date picker the user can select in the report as shown above.
Hi Julian,
I will now try to modify the approach and provide a link to the workbook for you to look at below.
Assets are buildings, that gives me better context.
Firstly, the transaction and the valuation table should be married:
AllTransactions =
UNION (
SELECTCOLUMNS (
'Asset Transaction',
"Asset Reference", 'Asset Transaction'[Asset Reference],
"TransactionDate", 'Asset Transaction'[Transaction Date],
"Type", 'Asset Transaction'[Transaction Type],
"Amount", 'Asset Transaction'[Net Transaction Price]
),
SELECTCOLUMNS (
'Asset Valuation',
"Asset Reference", 'Asset Valuation'[Asset Reference],
"TransactionDate", 'Asset Valuation'[Valuation Date],
"Type", "Validation",
"Amount", 'Asset Valuation'[Asset Value]
)
)
Now the full history is in the table from purchase to evaluation to potential sales is in one table.
I think to have understood that the date filter is only for defining the window that is shown in the visual. If this is the case then we can try a brute force approach with no very complicated measures. I come to the limitations later on.
BruteForceCrossJoin =
CROSSJOIN (
SELECTCOLUMNS ( DateTable, "Date", DateTable[Date] ),
DISTINCT (
SELECTCOLUMNS (
'Asset Transaction',
"Asset Reference", 'Asset Transaction'[Asset Reference]
)
)
)These are all combinations of Date and Asset. This will adress the problem that the visual does not carry over valuations to a the following years if there are no valuation dates. Now, the limitations: if you have thousands of assets this table is getting presumably to big. One thing, in any case is that you can restrict the date table to date with a new table Date which has a filter smaller than Today() on the date. Let's assume for the moment that this is working as the number of different assets is sufficiently small.
Now we can do a calculated column:
CurrentValue =
VAR thisAsset = BruteForceCrossJoin[Asset Reference]
VAR thisDate = BruteForceCrossJoin[Date]
VAR salesDate =
CALCULATE (
FIRSTNONBLANK ( AllTransactions[TransactionDate], 0 ),
FILTER (
AllTransactions,
AllTransactions[TransactionDate] <= thisDate
&& AllTransactions[Asset Reference] = thisAsset
&& AllTransactions[Type] = "Sale"
)
)
VAR lastValuationDate =
CALCULATE (
MAX ( AllTransactions[TransactionDate] ),
FILTER (
AllTransactions,
AllTransactions[TransactionDate] <= thisDate
&& AllTransactions[Asset Reference] = thisAsset
&& AllTransactions[Type] <> "Sale"
)
)
VAR lastValuationAmount =
CALCULATE (
FIRSTNONBLANK ( AllTransactions[Amount], 0 ),
FILTER (
AllTransactions,
AllTransactions[TransactionDate] = lastValuationDate
&& AllTransactions[Asset Reference] = thisAsset
)
)
RETURN
IF ( ISBLANK ( salesDate ), lastValuationAmount, 0 )So, now are dates have a value for all assets. If an asset is sold the value is 0. What concerns the remark above I am not sure if I understand the business context: if an asset is sold the futere value is zero. However, if I would like to still see it in the history. Please clarify.
In the model there only 3 tables now Date, BruteForceCrossJoin and Building. Relationships are Date and BruteForce via the dates and BruteForce and Building via the asset reference.
Now to the visual: I think the column year in the date table should be directly selected as the drill down of the date is essentially meaningless for the visual. Then we only want to show only the last valuation of the year summed over assets and risk class. Therefore we need a measure as value for the visual:
LatestValue =
VAR selectedYear =
SELECTEDVALUE ( DateTable[Year] )
VAR maxDateInYear =
CALCULATE (
MAX ( DateTable[Date] ),
FILTER ( DateTable, DateTable[Year] = selectedYear )
)
VAR selectedMaxDate =
SELECTEDVALUE ( DateTable[Date] ) //var maxDate = if (selectedMaxDate < maxDateInYear, selectedMaxDate, maxDateInYear)
RETURN
CALCULATE (
SUM ( BruteForceCrossJoin[CurrentValue] ),
FILTER ( BruteForceCrossJoin, BruteForceCrossJoin[Date] = maxDateInYear )
)
With a little helper filter for the assets, you see that the amount disappears, once sold:
is the one drive link to the workbook
If this is roughly the requirement then the calculated column approch works in principle.
Don't hesitate to reply: we will do this until we have both understood what is required.
Best regards
Christian
Thank you.
- the transaction table is still needed as we need the potential sales dates of the assets?
That is correct. The date will need to be referenced to understand if a previous Asset Valuation amount should be included in the current portfolio total up to the date the user has selected in the date picker. OR, if it has been marked as sold at a date previous to the date selected in the date picker, that this is then not included as part of the portfolio total, because you would be including asset values that are no longer owned.
- if we assume that we can keep the brute force approach for the moment, I think that a date selection will exactly achieve what we want, because if a sales date is after the upper filter date, it will not appear in the sequence of valuations for the asset?
Yes, I think this is correct.
Attached here I have updated your pbix attachment to include everything you have added where left off, but also to include the currency conversion slicer and logic for that to work. I'm open to this being written more efficiently, but for now it does the job.
I have created a Measure table to store some of the added measures instead of assigning them to a table (find it more organised this way)
In the Measure table:
- Selected Currency - returns the user selected currency
- ValueLastDate - returns the last value for an asset based on the LASTDATE DAX function
- ValueLastDate SUM - ensures totals are being displayed properly if displayed as a table
- ValueLastDate_CurrencyChanged - Takes the currency, the selected currency, LASTDATE of asset valuation, then finds the nearest currency converstion rate from the ECB imported table (or closest last day going back in time where there is conversion data). It then performs the converstion. As there are only conversions to EUR, sometimes there are two steps to the conversion if you are converting to anything other than the EUR, for example AUD to DKK has to be converted into then out of the EUR.
- ValueLastDate_CurrencyChanged SUM - is the same as the ValueLastDate SUM but uses the converted currency amount to summarise.
- I added a currency column to the Building table so that the above can reference the native currency of the asset for converting to/from
- I added a live feed for the ECB rates - ECB_FX_RATES table in the model
- In the DateTable I needed to add a couple of columns - ECBRateDate and isinECBTable to assist the dates of conversion alignment
- Added DistinctECBRates DAX table which gives DISTINCT rows for the ECB_FX_RATES dates available so it can cross reference.
You can see in the updated visuals I added on the Curency Conversion tab, this sort of works, and you can drill down to quarters etc. The values also convert to chosen currency if selected. But there is nothing for 2021/22/23 despite not all assets being Sold.
I hope this makes sense.
Hi Julian,
I indeed found a mistake in my calculated column for the conversion to Euro in the AllTransactions table. This fixes AAA103 as it seems.
For AAA107, I found this, what scares me :-):
I have 2 valuations per day in the original table, and the valuations are different.
Please let me if this is a bug, or if I am missing something.
Basically with this input, the difference can be explained as after sorting the lower value is picked. Or is there a rule: if there are 2 valuations on the same day take the bigger one?
Best regards
Christian
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
