DAX help for previous row calculation

[UM]

I am trying to calculate the projected inventory. Can someone help me in this.

[AlB]

Hi @UM

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.   

[UM]

Hi AIB , Thanks for your help. As suggested , i added Index and enter the codes. But i am still encountering error. When i try to use the solution, i encounter to use Min/MAX under variable declaration. Thus i tried with Min/MAX declaration and didnt get the desired out. Can please suggest what am i missing here ?

The desired output is :

But I am getting the below one in BI :

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi Ashish,

The solution is amazing.

The download link is not available.

Could you share the file to me?

[Ashish_Mathur]

You are welcome.  The link is working fine.  I just clicked on it.  Please try again.

[Anonymous]

It is working fine.

Thank you.

[UM · Accepted solution]

Thanks Ashish. It works great for me now.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.