DAX Measure for previous values based on dimension

[TOK]

I am struggeling with creating a measure with DAX.
I have an existing measure per two dimensions (ChainStoreID, ClusterID)

Now I want my measure to follow two simple rules.

1. If there is a blank value, get the next filled value (ordered by ClusterId) for this chainstore.

eg. there are three blank values (116, 1 ; 117, 7 ; 117, 8). As 116, 1 has no lower ClusterId, it should remain empty.
ClusterId 7 and 8 for ChainStoreId 117 should be filled with 87.

2. The current value should be the same or higher than the value of the previous clusterId.

eg. ClusterID 6 (88) is lower than ClusterId 5 (93), so it should be 93.

Current situation:

Expectation:

ChainStoreId	1	2	3	4	5	6	7	8
116		72	74	87	93	93	93	93
117	68	77	79	87	88	88	88	88

[TOK]

I tried both of your solutions and a calculated column worked best for me. 

But I guess my example wasn't specific enough. It works for Max(Value) having Max(ClusterID), but thats not always the case. ChainStoreID = 113 should return 78 for ClusterID 4 and 5, but our if clause won't return the expected as 78 is not max(Value) nor max(ClusterId).

Thanks for your help!

[tamerj1]

@TOK 
Ok you mean the maximum previous value right? This can be fixed

[tamerj1 · Accepted solution]

@TOK 
Try this https://www.dropbox.com/t/fEVKUullXZ43Unud

New Value = 
VAR CurrentValue = 
    Data[Value]
VAR CurrentClusterId =
    Data[ClusterId]
VAR CurrentStoreTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ChainStoreId] ) )
VAR PreviousClustersTable =
    FILTER ( CurrentStoreTable, Data[ClusterId] < CurrentClusterId )
VAR MaxValuePerStore =
    MAXX ( PreviousClustersTable, Data[Value] )
VAR ClusterIdOfMaxValue =
    CALCULATE ( 
        MAX ( Data[ClusterId] ), 
        PreviousClustersTable,
        Data[Value] = MaxValuePerStore 
    )
VAR Result =
    IF ( 
        CurrentValue < MaxValuePerStore
            && CurrentClusterId > ClusterIdOfMaxValue,
        MaxValuePerStore,
        CurrentValue
    )
RETURN
   Result

[tamerj1]

Hi @TOK 
Here is a sample file with the solutionhttps://www.dropbox.com/t/n3qHIrFTHyVIexTR

You can create a new calculated column 

New Value = 
VAR CurrentValue = 
    Data[Value]
VAR CurrentClusterId =
    Data[ClusterId]
VAR CurrentStoreTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ChainStoreId] ) )
VAR MaxValuePerStore =
    MAXX ( CurrentStoreTable, Data[Value] )
VAR ClusterIdOfMaxValue =
    CALCULATE ( 
        MAX ( Data[ClusterId] ), 
        CurrentStoreTable,
        Data[Value] = MaxValuePerStore 
    )
VAR Result =
    IF ( 
        CurrentValue < MaxValuePerStore
            && CurrentClusterId > ClusterIdOfMaxValue,
        MaxValuePerStore,
        CurrentValue
    )
RETURN
   Result

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Value measure: = 
SUM( Data[Value] )

Desired outcome measure: =
VAR currentclusterid =
    MAX ( 'Cluster'[ClusterID] )
VAR currentchainstoreid =
    MAX ( ChainStore[ChainStoreID] )
VAR currentvalue = [Value measure:]
VAR maxvalueperchainstore =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[ChainStoreID] = currentchainstoreid
                && Data[ClusterID] <= currentclusterid
        ),
        Data[Value]
    )
RETURN
    IF (
        currentvalue <= maxvalueperchainstore,
        maxvalueperchainstore,
        currentvalue
    )