How to carry over previous year value into year where value is not updated

[julesdude]

Hi all, 

I have a report where I have a bar graph visual. The X-axis is Year and Y-axis is value. It is driven from TableA in my model:

TableA

Reference	Value	Date
aaa	80	15/04/2022
aaa	20	24/07/2022
aaa	46	20/02/2020
aaa	84	02/09/2020
aaa	235	01/04/2018
aaa	69	01/02/2018
aaa	25	01/02/2017
aaa	24	21/10/2014
bbb	75	30/12/2021
bbb	95	28/11/2020
bbb	11	20/07/2018
bbb	66	16/07/2017
bbb	84	12/05/2016
bbb	64	01/02/2015
bbb	90	08/06/2014
bbb	36	02/04/2013

As things stand today, the date is December 2022. I want to display the SUM of values split by year. The problem in my current bar chart is that each time a year starts, the sum for that year starts at zero. Also, previous years contain gaps where the value against a reference may not be declared in a year - so it is not carried over a year or two where there is no value update/change. So if I'm looking at 2023's total values on the 1st January 2023, 2023 will have a value of zero as it's a new year and no values with a date in that year have been made.

What I need is for values to carry through the years. In the case of Reference aaa in TableA above, the last value of the reference in 2018 was on 01/04/2018 for 235. I would want that 235 to be part of the amount summed for the year 2018, to also be summed for 2019 but not 2020 as in that year there are two reviews of the value. The latest value of 84 would then take priority for 2020

There's also a cross check needed with TableB to see whether the reference was actually purchased or sold. If it has been sold and the date of being sold was, say, in 2020, then you'd not want to carry the value of that reference into 2021:

TableB

Reference	Purchase	Transaction Date
aaa	Purchase	01/01/2013
bbb	Purchase	01/04/2013
ccc	Sale	28/07/2022
ddd	Sale	22/06/2022

How do I achieve this? Grateful for any help.

[julesdude]

On the performance side of things, I have just brought in the data from my actual model into the solution template and joined the references in the DAX to the fields in the actual tables of data I shall be using. For a currency selection change, the bar chart takes about 72 seconds to update, with the main bottleneck being the DAX query according to the Performance Analyser. 

My Asset Transaction table contains 1323 rows. Asset Valuation has 7376. Building has just over 1000 rows.

Interestingly, once the visualisations update, they must be stored in the cache somewhere because if you cycle through each of the currencies and wait for the chart to update for each selection, then you cycle back to a currency that previously loaded the change is instant. 

The same delay occurs if you select any component of the visual itself, for example if you click on a specific asset reference or you select the asset reference from an applied slicer.

I have not tested to see whether the same calculation time occurs when the report is loaded to the Service but I'm guessing it will be similar. 

I think I am doing something wrong as I joined the data because now when I select EUR I get a strange mix that doesn't look right. If I select USD, the chart becomes completely blank. All other currencies look correct to me though.

[scee07]

Hi Julian, 
this is now difficult to judge as you can not share your company data. What I can do during the day,  is to synthesize the data to have bigger test data. But still the data you are describing is not big in data base terms and the measure is not overly complex. However, first step is that I increase my dataset size.

Best regards 

Christian

[scee07]

First guess,

my model has only 2 relationships

Second one, date hierarchy in the restricted date table has to  be selected.
Just, guesses. I will build a bigger dataset, to see what the issue is. In my case USD is blank as I have no USD exchange rates. We will see.

Best regards 

Christian

[scee07]

Just to make sure. This is the last version
Assets_180123_2.0.pbix

[julesdude]

The other thing I thought about here, if latency cannot be resolved, is if any of the legwork for this can be done further upstream by means of a dataflow. or in Power Query to prep the data in some way relieving some processing in the DAX or adding calculated columns to existing tables. Perhaps a table can be constructed in PQ with a subset of information, for example. I am wondering if that might ease the calculations somewhat or offer a different approach.

[julesdude]

Hi Christian,

Yes that is the last version. 

I think the other factor for slowdown might be that dates go back to 2010 in the Asset Transaction and Valuation tables. Perhaps due to the span of date size this has an impact on performance. 

The USD currency selection (which was changing the graphs to show no results -blank) - I figured out what was wrong. Simply, we had no USD FX rates!! In the request to the online ECB FX rates, I had not called USD in the URL string. I added it and it now pulls in USD FX data and the graphs I use in the report for USD currency selection are no longer blank.

So I only have the EUR issue where the data doesn't look quite right. I will work through this now to see what might be going wrong. All other currency selections return results that look correct. Currently looks like this:

But it should look like this (shows with other currencies):

[scee07]

I have done 2000 artficial buildings now and 5000 valuations. I can confirm that the measure is slow when aggregating (e.g. showing the values per risk profiles). Specfic assets are not the problem. For the view per risk profile there is the possibility to pre-aggregate and then offer a drilldown which assets are in scope. I will just do this. But it  will be until tomorrow.  
If this is acceptable, then your country alloaction would work the same way. 

So, the limits of the engine are achieved pretty quickly ....
At the moment I have no other idea to make the measure signifcantly quicker. Let's try this work around and see what it brings. 
Best regards 
Christian

[julesdude]

Many thanks Christian,

Basically I am splitting into 4 bar charts. Each chart has a different legend - the legend is defined by an attribute field in the Building table. Risk is the one we've been using in your solutions. That will be for one chart. The other three use Country, Region, and another category.

But yes I can adapt and apply any of the pre-aggregation logic you mentioned to accomodate these other 3 types of chart/legend combinations I am showing. 

Thank you again for your time on this. I am still working through the EUR issue.

[scee07]

Hi Julian, 

this is just a heads up. When pre-calculating the exchange rates for the transactions the performance issue will go away. 

I played around with a higher number of assets and transactions.
Assets_Performance_190123.pbix

I will transfer this approach to the original file tomorrow. 

Best regards 
Christian

[julesdude]

That has made a big difference. Refresh is now instant and no delay. It definitely looks like this is a better approach. 

[scee07]

Hi Julian, 
I have finished not the adjustment to the original file. Basically the All Transactions File is extended and then the measure is simplified. 

AllTransactionsExtended =
CROSSJOIN (
    SELECTCOLUMNS ( Currencies, "TargetCurrency", Currencies[Currencies] ),
    AllTransactions
)

EuroAmount =
VAR thisDate = AllTransactionsExtended[TransactionDate]
VAR originalAmount = AllTransactionsExtended[Amount]
VAR originalAssetCurrency = AllTransactionsExtended[Currency]
VAR maxDate =
    CALCULATE (
        MAX ( ECB_FX_RATES[TIME_PERIOD] ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] <= thisDate
                && ECB_FX_RATES[CURRENCY] = originalAssetCurrency
        )
    )
VAR exchangeRate =
    CALCULATE (
        FIRSTNONBLANK ( ECB_FX_RATES[OBS_VALUE], 0 ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] = maxDate
                && ECB_FX_RATES[CURRENCY] = originalAssetCurrency
        )
    )
RETURN
    IF (
        originalAssetCurrency = "EUR",
        originalAmount,
        originalAmount / exchangeRate
    )
TargetCurrencyAmount =
VAR thisDate = AllTransactionsExtended[TransactionDate]
VAR euroAmount = AllTransactionsExtended[EuroAmount]
VAR originalAmount = AllTransactionsExtended[Amount]
VAR originalAssetCurrency = AllTransactionsExtended[Currency]
VAR targetCurrency = AllTransactionsExtended[TargetCurrency]
VAR maxDate =
    CALCULATE (
        MAX ( ECB_FX_RATES[TIME_PERIOD] ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] <= thisDate
                && ECB_FX_RATES[CURRENCY] = targetCurrency
        )
    )
VAR exchangeRate =
    CALCULATE (
        FIRSTNONBLANK ( ECB_FX_RATES[OBS_VALUE], 0 ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] = maxDate
                && ECB_FX_RATES[CURRENCY] = targetCurrency
        )
    )
RETURN
    IF (
        originalAssetCurrency = targetCurrency,
        originalAmount,
        IF ( targetCurrency = "EUR", euroAmount, euroAmount * exchangeRate )
    )

Above all combinations of target currencies and transactions are taken, the  euro value and the target currency value is calculated.  This is the work that was before in the measure and is now precalculated. 

The measure simplifies to

LatestValidationInCurrency =
VAR thisCurrency = [Selected Currency]
VAR thisAsset =
    SELECTEDVALUE ( Building[Asset Reference] )
VAR maxDate = [Max Selected Date]
VAR lastValidationDate =
    CALCULATE (
        MAX ( AllTransactionsExtended[TransactionDate] ),
        FILTER (
            ALL ( AllTransactionsExtended ),
            AllTransactionsExtended[Asset Reference] = thisAsset
                && AllTransactionsExtended[TransactionDate] <= maxDate
                && AllTransactionsExtended[Type] = "Validation"
                && AllTransactionsExtended[TargetCurrency] = [Selected Currency]
        )
    )
VAR validationValue =
    CALCULATE (
        FIRSTNONBLANK ( AllTransactionsExtended[TargetCurrencyAmount], 0 ),
        FILTER (
            ALL ( AllTransactionsExtended ),
            AllTransactionsExtended[Asset Reference] = thisAsset
                && AllTransactionsExtended[TransactionDate] = lastValidationDate
                && AllTransactionsExtended[TargetCurrency] = [Selected Currency]
        )
    )
VAR globalMaxValidationDate =
    CALCULATE (
        MAX ( AllTransactionsExtended[TransactionDate] ),
        FILTER (
            ALL ( AllTransactionsExtended ),
            AllTransactionsExtended[Asset Reference] = thisAsset
                && AllTransactionsExtended[Type] = "Validation"
                && AllTransactionsExtended[TargetCurrency] = [Selected Currency]
        )
    )
VAR globalLastValue =
    CALCULATE (
        FIRSTNONBLANK ( LatestValuationsExtended[TargetCurrencyAmount], 0 ),
        FILTER (
            LatestValuationsExtended,
            LatestValuationsExtended[Asset Reference] = thisAsset
                && LatestValuationsExtended[TargetCurrency] = [Selected Currency]
        )
    )
VAR wasSold =
    COUNTROWS (
        FILTER (
            ALL ( AllTransactions ),
            AllTransactions[TransactionDate] <= maxDate
                && AllTransactions[Type] = "Sale"
                && AllTransactions[Asset Reference] = thisAsset
        )
    ) > 0
VAR result =
    IF (
        wasSold,
        0,
        IF ( ISBLANK ( validationValue ), [GlobalLastValidationValue], validationValue )
    )
RETURN
    result

Assets_200123.pbix

What I tested, Euro is working and complete other currencies as well.

A currency is complete when every transaction finds an exchange rate. In your original data my recommendation is to do some work on master data consistency. Ideally you do an exception report, tie to a dashboard in the service and send yourself an alert. 

I am OOF tomorrow. We can talk next week. 

Best regards 

Christian

[julesdude]

Hi Christian,

Thank you. The data contained in this latest attachment works fine and displays correctly. I migrated my data over to the solution but the visualisations started to display strangely. Unlike the solution Assets_180123_2.0 you provided which had some delay in processing but all except EUR currencies were displaying correctly when I transferred my data over to it, now with this one, all currencies display strangely in the visualisation:

MYR:

EUR:

I will look into the data again tomorrow in more detail. Perhaps I will add a bit of data at a time to see where the inconsistency is as I am unsure right now. I made sure the data modelling relationships were identical too, so it is strange to me but there must be something in the data that is really skewing the values. Interestingly an 'i' icon appears in the top right of the visual. WHen I hover the mouse over it it tells me 'chart data contains infinity values'.

Thanks again.

[scee07]

Hi Julian, 
when you create the ExtendedAllTransactionTable above with your original data, does the converted value in the target currency contain infinite values? If yes this is carried over to the visual. This happens if for a specific transaction no exchange rate can be found. In my test data I only used complete currencies in this sense.

If you look at the measure above, there is no division by exchange rate executed anymore. This means the measure cannot produce infinity.

This is where infinity happens:

to get the euro value, we divide by the exchange rate. If not there, it produces infinity.

EuroAmount =
VAR thisDate = AllTransactionsExtended[TransactionDate]
VAR originalAmount = AllTransactionsExtended[Amount]
VAR originalAssetCurrency = AllTransactionsExtended[Currency]
VAR maxDate =
    CALCULATE (
        MAX ( ECB_FX_RATES[TIME_PERIOD] ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] <= thisDate
                && ECB_FX_RATES[CURRENCY] = originalAssetCurrency
        )
    )
VAR exchangeRate =
    CALCULATE (
        FIRSTNONBLANK ( ECB_FX_RATES[OBS_VALUE], 0 ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] = maxDate
                && ECB_FX_RATES[CURRENCY] = originalAssetCurrency
        )
    )
RETURN
    IF (
        originalAssetCurrency = "EUR",
        originalAmount,
        originalAmount / exchangeRate
    )

I am not available for the rest of the day. 

Next week we can write a short check report to check all master data for a given data input. 

Best regards 

Christian

[julesdude]

Hi Christian, a good weekend to you.

Thank you for the above explanation. I now understand that the DAX created AllTransactionsExtended table is a consolidated list of all value related activity related to an asset in the one table. Each row contains either a purchase amount, sale amount, and 'Validation' items where the value is updated over time taken from records in the Asset Valuatoin table. We also have the related currency to the asset.

In this DAX table we also have a couple of calculated columns - EuroAmount shows conversion of the Amount value from the asset's native currency (Currency column) into EUR using the TransactionDate - using the rate from the ECB_FX_RATES table for this currency on the same date. The TargetCurrencyAmount column is similar. It converts the Amount value to the currency displayed in the TargetCurrency column:

In fact, we get all combinations per row here. So as you can see the TargetCurrency column cycles through all currencies we have called in through the ECB web API and cycles through them for each transaction, providng the conversion rate.

As I looked into the infinity values more deeply, I used the data table view as above to filter anomalies in the TargetCurrencyAmount and EuroAmount columns as you suggested.

Many displaying the EuroAmount as blank, very low number or '∞' symbol.

On reflection I think I have identified some if not all the issues creating these.

1. We only get approx 22 years of data through the SDMX catalogue for ECB exchange rates. This explains why EuroAmount and TargetCurrencyAmount is blank in some cases because there are no entries in the ECB_FX_RATES table with the same TransactionDate. The ECB web API call brings back FX data starting from 04-Mar-1999 but not before. I will obtain what rates are being used for these by asking internal sources. I will need to manually create an Excel sheet with them on and feed this through PQ and merge the query to the existing ECB_FX_RATES. But for now, we could just filter these out completely while I wait for that.

2. For the data we DO get back from ECB_FX_RATES, some FX rows have a date stamp but a blank FX rate. I do not know why this is. Some seem to be public holidays. Others I am not so sure. I think to overcome this, we would not only need to put in the logic to look for the most recent matching date as we are already doing if the date falls on a weekend, but also where the OBS_VALUE does not contain a blank, so I guess some use of the LASTNONBLANK function with the MAX function. 

3. For a number of 'Sale' and 'Purchase' Types there is no amount provided in the Asset Transaction table - it is blank. Similarly some values in the Asset Valuation and Asset Transaction tables return low value amounts for assets like 1 or 0.01. This is correct, but something I need to enquire about for the data my side. It is the data in the system I am getting this from, so is correct in a sense, but I will need to 'cleanse' this at some point.

4. Both the EuroAmount and TargetCurrencyAmount columns have a number of ∞ symbols in them in the data view - the infinity symbol. Vast majority of these are expected because the TransactionDate column of the row precedes the start of the range of the FX rates from the ECB_FX_RATES table. Apart from those, there are a remaining 14 rows containing TransactionDate of 01/05/2012 and 01/01/2009. There ARE entries in the ECB_FX_RATES table with these dates, but, this is where the OBS_VALUE column in the ECB_FX_RATES table is blank. Mitigiating this problem in point 2 above should solve this issue if possible. 

[scee07]

Hi Julian,
as you are saying the valuation part is your internal process. Exception reporting is rather trivial filters, like:
- valuation is there and has blank entry

- assset has no validation yet

- valuation amount is questionnable
This is perdominantly a business process question that you will deal with internally.
What concerns the integrity of the exchange rates, I have a few ideas I would like to share with you. 
Firstly, I extended the AllTranscationsExtended table with the following columns:

OrginalCurrencyRateFound =
VAR thisDate = AllTransactionsExtended[TransactionDate]
VAR originalAmount = AllTransactionsExtended[Amount]
VAR originalAssetCurrency = AllTransactionsExtended[Currency]
VAR maxDate =
    CALCULATE (
        MAX ( ECB_FX_RATES[TIME_PERIOD] ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] <= thisDate
                && ECB_FX_RATES[CURRENCY] = originalAssetCurrency
                && ISBLANK ( ECB_FX_RATES[OBS_VALUE] ) = FALSE ()
        )
    )
VAR exchangeRate =
    CALCULATE (
        FIRSTNONBLANK ( ECB_FX_RATES[OBS_VALUE], 0 ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] = maxDate
                && ECB_FX_RATES[CURRENCY] = originalAssetCurrency
                && ISBLANK ( ECB_FX_RATES[OBS_VALUE] ) = FALSE ()
        )
    )
RETURN
    IF (
        originalAssetCurrency = "EUR",
        TRUE (),
        IF ( ISBLANK ( exchangeRate ) || exchangeRate = 0, FALSE (), TRUE () )
    )

TargetCurrencyRateFound =
VAR thisDate = AllTransactionsExtended[TransactionDate]
VAR targetCurrency = AllTransactionsExtended[TargetCurrency]
VAR maxDate =
    CALCULATE (
        MAX ( ECB_FX_RATES[TIME_PERIOD] ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] <= thisDate
                && ECB_FX_RATES[CURRENCY] = targetCurrency
                && ISBLANK ( ECB_FX_RATES[OBS_VALUE] ) = FALSE ()
        )
    )
VAR exchangeRate =
    CALCULATE (
        FIRSTNONBLANK ( ECB_FX_RATES[OBS_VALUE], 0 ),
        FILTER (
            ECB_FX_RATES,
            ECB_FX_RATES[TIME_PERIOD] = maxDate
                && ECB_FX_RATES[CURRENCY] = targetCurrency
        )
    )
RETURN
    IF (
        targetCurrency = "EUR",
        TRUE (),
        IF ( ISBLANK ( exchangeRate ) || exchangeRate = 0, FALSE (), TRUE () )
    )
OriginalCurrencyAcceptableExchangeRate =
VAR thisCurrency = AllTransactionsExtended[Currency]
VAR thisTransactionDate = AllTransactionsExtended[TransactionDate]
VAR dateTreshold = thisTransactionDate - 7
RETURN
    IF (
        thisCurrency = "EUR",
        TRUE (),
        COUNTROWS (
            FILTER (
                ECB_FX_RATES,
                ECB_FX_RATES[CURRENCY] = thisCurrency
                    && ECB_FX_RATES[TIME_PERIOD] <= thisTransactionDate
                    && ECB_FX_RATES[TIME_PERIOD] >= dateTreshold
            )
        )
    ) > 0
TargetCurrencyAcceptableExchangeRate =
VAR thisCurrency = AllTransactionsExtended[TargetCurrency]
VAR thisTransactionDate = AllTransactionsExtended[TransactionDate]
VAR dateTreshold = thisTransactionDate - 7
RETURN
    IF (
        thisCurrency = "EUR",
        TRUE (),
        COUNTROWS (
            FILTER (
                ECB_FX_RATES,
                ECB_FX_RATES[CURRENCY] = thisCurrency
                    && ECB_FX_RATES[TIME_PERIOD] <= thisTransactionDate
                    && ECB_FX_RATES[TIME_PERIOD] >= dateTreshold
            )
        )
    ) > 0

This can be written shorter as we use some parts in the calculation of the actual exchange rates. But for explanation purposes this if fine. 

We calculate for every record if 

- an exchange rate for the original currency up to the transaction date exists

- an exchange rate for the  target currency exists 

- an exchange rate for the original exchange rate in the time window exists. A time window is minimum n days before the transactions date, but up to the transaction date 

- an exchange rate for the target exchange rate in the time window exists. A time window is minimum n days before the transactions date, but up to the transaction date 

Then we create drillthrough tables like:

MissingOriginalCurrencyDrillDown =
FILTER (
    AllTransactionsExtended,
    AllTransactionsExtended[OrginalCurrencyRateFound] = FALSE ()
)

Now, I scetched a panel "CurrencyOverview" where you can do drillthroughs in the four cases. 

(Right click in the currency, select drillthrough, use the "back" arrow in the break down  to get back to the overview.

Let me know if this is useful. In principle this is a separate report. The next step would be to define a simple alert measure, like "count all drillthrough table rows", make a visual, put it on   dashboard and send yourself an alert if the number is not 0. 

Assets_220123.pbix

Best regards 
Christian

[julesdude]

Hi Christian,

I think this will be helpful to identify anomalies in the conversion process. I realised that your report and creation of tables AllTransactions and AllTransactionsExtended actually help provide an easier way of finding these. I could definitely come back to this and the tables running somewhere , extracted to a Dashboard and set some alerts to that. 

Ultimately I think that I need to filter out the infinity errors here, or anything else that might be causing the graphs to skew so much, just to ensure it is working as it should with my data.

For the data issues which are of internal process related and logged in the system I extract this from- i.e. getting very low values for assets such as 1 and less than 1 - these are actually legitimate values which need to be kept in. They are legitimate because the transaction was actually for that value, so they need to be kept in.

I suspect then the issue is with the values that return as blanks, so I will filter these out and run again.

Should this then work, the only remaining piece to the puzzle is that I need to apply an ownership percentage to the value. In other words, I have a couple of tables that list a percentage ownership value and this percentage must be applied to the values used in the Asset Transaction and Asset Valuation tables. The effects only a handful of assets, so only a small proportion of the portfolio, and the percentage would be applied before any currency conversion. 

Thank you for this. I will run the test taking out the above blanks as I mentioned. I think best to use your latest attachment to see how the graphs look.

[scee07]

OK, but hold on a little. I think the performance problem is that for every asset in the selection (let's say the selection is risk profile and quarter of a specific year the latest conversion rate is calculated when rendering the visual), We can avoid this by precalculating all currency conversions as table field. This will increase the valuation and transaction by the factor number of currenciees, but when rendering the visual this is only a filter by selected currency. Let's what this brings. I will explain this in more detail when coming back to you. 

Best regards 
Christian

[scee07]

Hi Julian, 

I am busy for the rest of the day and come back to you tomorrow.
I will delete the lower values for test purposes and check  a few cases. I will then send the updated file.  At the moment the date type for the valuation dates is Date. If this is changed to Date/Time (like a timestamp) in the real input, then last entry is automatically pulled. 
Best regards 
Christian

[julesdude]

Thanks Christian. Of course, I am just grateful for your help.

You could change data type to time series, but I think this is not necessary. In fact, I just checked my actual dataset and I only receive just the date anyway - there is no time related data.

Many thanks again.

[scee07]

Hi Julian, 

I am stuck with a customer, will be until tomorrow when I'll come back to you. 

Best regards 
Christian

[julesdude]

Thanks Christian. No problem. I have not been progressing on it much at all myself.