Need help please on DAX: Calculated Column

Hi Anonymous 

when I reached the part when you said "If the previous week's Opening Stock After is negative, 0 should be used in the calculation of the current week's " I was like 😅

I don't think it's going to be easy. However, I have something in mind, let me try apply it tomorrow morning. Anyway, this is not going to be efficient by any means so I hope your data is not that big. 

Good morning Anonymous 

Please refer to attached sample file with the solution. 

I have to mention that this solution is not perfect. There could be some cases where it might not provide correct results however, such cases are extremely rare. Anyway the solution is already complex and tying to improve further will result in even more complex and less efficient solution which I guess won't be necessary. Please let me know if you have any further questions.

 

Opening Stock After = 
VAR CurrentWeek1 = Data[Week Number]
VAR OpeningStock1 = Data[Opening Stock]
VAR CurrentUniqueTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Unique] ) )
VAR StockAfterTable = 
    ADDCOLUMNS ( 
        CurrentUniqueTable, 
        "@StockAfter", 
        VAR CurrentWeek2 = [Week Number]
        VAR TableBefore = FILTER ( CurrentUniqueTable, [Week Number] < CurrentWeek2 )
        VAR Value1 =
            SUMX ( 
                TableBefore, 
                [Proposed Shipment] - [Demand]
            ) 
        VAR Value2 = Value1 + OpeningStock1
        RETURN
            IF ( Value2 < 0, 0, Value2 )
    )
VAR CurrentRecord = FILTER ( StockAfterTable, [Week Number] = CurrentWeek1 )
VAR PreviousRecord = FILTER ( StockAfterTable, [Week Number] = CurrentWeek1 - 1 )
VAR OpeningStock2 = MINX ( CurrentRecord, [@StockAfter] )
VAR Result =
    IF ( OpeningStock2 = 0, SUMX ( PreviousRecord, [Proposed Shipment] - [Demand] ), OpeningStock2 )
RETURN
    IF ( Result < 0, 0, Result )