Lookup maximum value based on chosen period

[ben_w]

I've got a need to multiply [Budget Cost] by [Percentage of completion] to get the budget that is already allowed to be spent, we call it [Free budget].

The [Percentage of completion] is saved for every combination of [Instance], [Project] [LineNr] and [UpdateId]

The [Percentage of completion] changes every month and gets a new record with a different [UpdateId].

The [Instance], [Project] [LineNr] define the level of detail the % gets updated for.

The [Free budget] has to be dynamic in terms of time, if the user puts a filter on a certain month, the percentage of that month has to be used (Last possible [ChangeId] for that month).

[Budget Cost] is in the table [Fact_Sales_Line_Detail]

[Percentage of completion] is in the table [Fact_Progress_Status_Line]

I have written the following query in [Fact_Sales_Line_Detail].

I use MAX() in the filter set to get the latest possible [UpdateId]. The table [Fact_Progress_Status_Line] is linked to the date dimension so the latest [UpdateId] available for a combination will always be the correct one to choose.

Free budget :=
SUMX (
    Fact_Sales_Line_Detail;
    [Budget Cost]
        * (
            CALCULATE (
                VALUES ( Fact_Progress_Status_Line[Percentage of completion] );
                FILTER (
                    Fact_Progress_Status_Line;
                    Fact_Progress_Status_Line[Instance] = Fact_Sales_Line_Detail[Instance]
                        && Fact_Progress_Status_Line[Project] = Fact_Sales_Line_Detail[Project]
                        && Fact_Progress_Status_Line[LineNr] = Fact_Sales_Line_Detail[LineNr]
                        && Fact_Progress_Status_Line[UpdateId]
                            = MAX ( Fact_Progress_Status_Line[UpdateId] )
                )
            )
        )
)

Yet the measure returns exactly nothing, i've tried ways with LOOKUPVALUE but haven't gotten to a working solution either. -I think- the main issue lies in determining the latest [UpdateId] for a combination.

[v-sihou-msft]

@ben_w

You want to lookup [Percentage of completion]  based [Instance], [Project] [LineNr] and [UpdateId], but [UpdateId] should be the [UpdateId] under same [Instance], [Project] [LineNr]. Right?

In this scenario, you should calcualte the MAX [UpdateId] first, and within FILTER() function, the matched value in condition should be aggregated. So your measure should be like: 

Free budget =
VAR maxUpdateId =
    CALCULATE (
        MAX ( Fact_Progress_Status_Line[UpdateId] ),
        ALLEXCEPT (
            Fact_Progress_Status_Line,
            Fact_Progress_Status_Line[Instance],
            Fact_Progress_Status_Line[Project],
            Fact_Progress_Status_Line[LineNr]
        )
    )
RETURN
    SUMX (
        Fact_Sales_Line_Detail,
        [Budget Cost]
            * (
                CALCULATE (
                    VALUES ( Fact_Progress_Status_Line[Percentage of completion] ),
                    FILTER (
                        ALL ( Fact_Progress_Status_Line ),
                        Fact_Progress_Status_Line[Instance] = MAX ( Fact_Sales_Line_Detail[Instance] )
                            && Fact_Progress_Status_Line[Project] = MAX ( Fact_Sales_Line_Detail[Project] )
                            && Fact_Progress_Status_Line[LineNr] = MAX ( Fact_Sales_Line_Detail[LineNr] )
                            && Fact_Progress_Status_Line[UpdateId] = maxUpdateId
                    )
                )
            )
    )

Regards,

[ben_w]

@v-sihou-msft

I've tried many variations on your code but the passing of the "parameters(filters)" never seems to go through. Even if i take away the allexcept to avoid issues there, nothing is returned.

It makes we wonder if getting a value dynamically from another table for every row with "parameters" of the evaluated row is even possible?