Running Prices in filter context with different granularities

[I_LOVE_POWER_BI]

So here is my business scenario:

• I have negotiated prices for certain periods on Material Type level
• Based on the column Order Priority the to-use Material Type is defined on the higher Material Master level

In this case, "Sugar Type B" price shall be shown on Material Master Level (highlighted in green), because of the "1" in the order priority.

That worked fine based on this MEASURE:

Top Order Priortiy Price = 
VAR MinOrderPriority = MIN (Prices[Order Priority]) 
VAR Top1 = 
TOPN (
    1,
    VALUES ( Prices[Order Priority] ),
    [MinOrderPriority ],
    ASC
)
VAR Result = CALCULATE( [Material Price], Top1)
RETURN
Result

Next step, I add a running price. I repeat the last available negotiated price every month until a new negotiated price is available (to put it on a line chart, without blanks). In a table format it looks like this:

However, as you can see, the red highlighted result is not what I need. My goal is that the Material Type Price with the MIN Order Priority will always be shown on Material Master level. Right now this is not the case, because Sugar Type B has only a negotiated price in 2022-01, whereas Sugar Type A has negotiated price in 2022-04 - therefore starting 2022-04, the TOPN measure on Material Master will show Type A, due to the evaluation context. Here the MEASURE I used:

Top Order Priority Price Running = 
VAR LastNonBlankDate =
    CALCULATE (
        MAX ('Calendar'[Year Month]),
        FILTER (
            ALL ( 'Calendar'[Year Month] ),
            'Calendar'[Year Month] <= MAX ( 'Calendar'[Year Month] )
            && [Top Order Priority Price] <> 0
        )
    )
RETURN
    CALCULATE (
        [Top Order Priority Price],
        FILTER (
            ALL ( 'Calendar'[Year Month] ), 
            'Calendar'[Year Month] = LastNonBlankDate
        )
    )

In my mind the solution shouldn't be that complicated, I assume I should remove some filter context or use virtual tables somewhere....I am just too stupid I guess, can't produce a result that works after many many hours of failing....Also my goal is to use a measure without adding a physical table, in order to keep dynamic slicer possibilities and reduce storage. An additional column that somehow works would be fine of course...

Here again my desired outcome on only Material Master level:

Thanks a lot for your kind support!

[I_LOVE_POWER_BI · Accepted solution]

Thank you! I solved it by now.

[v-luwang-msft]

Hi @I_LOVE_POWER_BI ，

Could you pls share your pbix file and remember to remove confidential data.

Best Regards

Lucien