Semi-additive balances interacting in different tables

[david230796]

Hi All,

Relatively new to the Power BI journey but really enjoying the problem-solving so far. Run into a bit of a roadblock however and hope I can get some help from this amazing community:

Scenario:

I have a number of products which are valued quarterly. These are semi-additive in that I want to sum their individual values on each day to get my portfolio's value but I don't want to sum these values on different dates as they are effectively balances. In order to show the value of the portfolio on dates that can be filtered, I have created a measure ("Most Recent Valuation") to calculate the most recent valuation of each product.

This measure is working well however my problem comes where there are some products which have been sold and therefore I don't want to include them in the most recent valuation after a certain date (the date they were sold). I need them to appear in the portfolio valuations right up until the date they were sold and then not to be included thereafter.

I have a table which lists out when the products were disposed of and I have created a calculated column to get the last relevant valuation date for each of the products - in the hope that I would be able to include that in my measure to filter out results after that. I've been pretty unsuccessful in that and so any pointers on this would be really appreciated.

I have a dates table as well as the two tables mentioned above - shown below:

Valuations

Product Reference	Date	Valuation
A	30-Jun-2020	100
B	30-Jun-2020	200
C	30-Jun-2020	300
A	30-Sep-2020	150
C	30-Sep-2020	350

Disposals

Product Reference	Date of Disposal	Date of Last Valuation
B	19-Jul-2020	30-Jun-2020

Most Recent Valuation = 
VAR MaxDate =
    MAX ( 'Dates'[Date] )
VAR LastDates =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( Valuations[Product Reference] ),
            ALL ( 'Dates' )
        ),
        "LastDay", CALCULATE (
            MAX ( Valuations[Date] ),
            ALL ( 'Dates' ),
            'Dates'[Date] <= MaxDate
        )
    )
VAR LastDatesWithLineage =
    TREATAS (
        LastDates,
        Valuations[Product Reference],
        'Dates'[Date]
    )
VAR Result =
    CALCULATE (
        SUM ( Valuations[Valuation]),
        LastDatesWithLineage
    )
RETURN
    Result

At the moment the measure above returns the following matrix:

Product Reference	Most Recent Valuation
A	150
B	200
C	350

I essentially want to find a way to exclude B from the results for 30 September 2020, while keeping it included in 30 June 2020.

Thanks for reading this, hope someone can help.

[david230796]

Thanks so much @DataInsights - that's working perfectly. Really appreciate your time and effort.

[DataInsights · Accepted solution]

@david230796,

Try this measure:

Most Recent Valuation = 
VAR vSlicerDate =
    MAXX ( ALLSELECTED ( Dates ), Dates[Date] )
VAR vProductMaxDateInScope =
    CALCULATE (
        MAX ( Valuations[Date] ),
        ALL ( Dates ),
        Valuations[Date] <= vSlicerDate
    )
VAR vProductValuation =
    CALCULATE (
        SUM ( Valuations[Valuation] ),
        ALL ( Dates ),
        Valuations[Date] = vProductMaxDateInScope
    )
VAR vResult =
    IF ( MAX ( Valuations[Date] ) = vProductMaxDateInScope, vProductValuation )
RETURN
    vResult