DAX help for previous row calculation

Hi Anonymous

This would seem to lend itself better to excel but let's give it a try.

1. Create a column with week numbers. Extract the week number from the the Attribute column ("Wk 1" will be a 1 (number, not text)). Assign, for instance, a -1 to "Past Wk". We can then use this new column as index. Let's call it WeekNum.

2. Use something along these lines for the Projected inventory column:

VAR CurrentWk = Table1[WeekNum]
VAR CurrentSOH = Table1[SOH]
VAR PreviousSOH =
    CALCULATE (
        LASTNONBLANK ( Table1[SOH]; 1 );
        Table1[WeekNum] < CurrentWk;
        ALLEXCEPT ( Table1; Table1[Part] )
    )
VAR Sup_Dem = Table1[Supply] - Table1[Demand]
RETURN
    IF ( ISBLANK ( PreviousSOH ); CurrentSOH; PreviousSOH + Sup_Dem )

Note the above code bears the implicit assumption that the weeks are sorted as you show. Were that not the case,  the LASTNONBLANK would yield an erroneus number. You wold then need to check numerically for the previous week number.

In actual fact, the column with the WeekNum might not be necessary. I believe we could use the Attribute column as (proxy to an) index as the values are sorted alphabetically.