Power BI - Filtering model on latest version of all attributes of all dimensions through DAX

I have a model that's comprised of multiple tables containing, for every ID, multiple rows with a valid_from and valid_to dates.

This model has one table in that is linked to every other table (a table working as both a fact and a dimension).

This fact has bi-directional cross filtering with the other tables.

I also have a date dimension that is not linked to any other table.

I want to be able to calculate the sum of a column in this table in the following way:

• If a date range is selected, I want to get the sum of the latest value per ID from the fact able that is before the max selected date from the date dimension.

• If no date is selected, I want to get the sum of the current version of the value per ID.

This comes down to selecting the latest value per ID filtered on the dates.

Because of the nature of the model (bi-directional with the fact/dimension table), I want to have the latest version of any attribute from any dimension selected in the visual.

Here's an data example and the desired outcome:

fact/dimension table:

Dimension 1:

Dimension 2:

My measure is supposed to do the following:

• If no date is selected than the result will be a matrix like the following:

• If July 2021 is selected than the result will be a matrix like the following:

So the idea here is that the measure would filter the fact table on the latest valid value in the selected date range, and then the bi-directional relationships will filter the dimensions to get the corresponding version to that row with the max validity (last valid row) in the selected range date.

I have tried to do the following two DAX codes but it's not working:

Solution 1: With this solution, filtering on other dimensions work and I get the last version in the selected date range for all attributes of all used dimensions. But the problem here is that the max valid from is not calculated per ID, so I only get the max valid from overall.

Amount Measure=
VAR _maxSelectedDate = MAX(Dates[Dates])
VAR _minSelectedDate = MIN(Dates[Dates])
VAR _maxValidFrom = 
    CALCULATE(
        MAX(fact[valid_from]),
        DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate)
        || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate)
    )

RETURN
CALCULATE(
    SUM(fact[Amount]),
    fact[valid_from] = _maxValidFrom
)

Solution 2: With this solution, I do get the right max valid from per ID and the resulting number is correct, but for some reason, when I use other attributes from the dimensions, it duplicates the amount for every version of that attribute. The bi-directional filtering does not work anymore with Solution 2.

Amount Measure=
VAR _maxSelectedDate = MAX(Dates[Dates])
VAR _minSelectedDate = MIN(Dates[Dates])
VAR _maxValidFromPerID = 
    SUMMARIZE(
        FILTER(
            fact,
            DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate)
            || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate)
        ),
        fact[ID],
        "maxValidFrom",
        MAX(fact[valid_from])
    )

RETURN
    CALCULATE(
        SUM(fact[Amount]),
        TREATAS(
            _maxValidFromPerID,
            fact[ID],
            fact[valid_from]
        )
    )

So if somebody can explain why the bi-directional filtering doesn't work anymore that will be great, and also, more importantly, if you have any solution to have both the latest value per ID and still keep filtering on other attributes, that would be great!

Sorry for the long post, but I thought it's best to give all the details for a complete understanding of my issue, this has been picking my brain since few days now and I'm sure I'm missing something stupid but I turned to this community for help because I cannot seem to be able to find a solution!

Thank you very much in advance for any help!

Seems to be workable with a dummy model. I didn't got the point how filter ID, so if it creates a problem let me know how you handle ID. Then I changed fact to facts as fact is a function. Also, I'm not sure about the workability of the measure at your real model. Hope you will give some feedback.

Amount Measure = 
VAR ValidDate=
        calculate(
            max(facts[Valid_to])
            ,ALLEXCEPT(facts,facts[ID])
            ,facts[Valid_to]<=MAX(Dates[Date])
        )
Return
    CALCULATE(
        SUM(facts[Amount])
        ,TREATAS({ValidDate},facts[Valid_to])
    )