Track inventority with pivot table

Anonymous ,

Create a calculate column Initial:

Initial = 
VAR Current_billing = CALCULATE(SUM(Table1[quantity]), FILTER(ALLEXCEPT(Table1, Table1[week]), Table1[type] = "billing"))
VAR Current_consignment = CALCULATE(SUM(Table1[quantity]), FILTER(ALLEXCEPT(Table1, Table1[week]), Table1[type] = "consignment"))
RETURN
Current_consignment - Current_billing

Then create a calculate column Mid Step:

Mid Step = 
VAR Current_Week = Table1[week]
VAR Previous_Week = CALCULATE(MAX(Table1[week]), FILTER(Table1, Table1[week] = Current_Week - 1))
VAR Current_Initial = CALCULATE(MAX(Table1[Initial]), FILTER(Table1, Table1[week] = Previous_Week))
VAR Current_billing = CALCULATE(MAX(Table1[quantity]), FILTER(Table1, Table1[type] = "billing" && Table1[week] = Current_Week))
VAR Current_consignment = CALCULATE(MAX(Table1[quantity]), FILTER(Table1, Table1[type] = "consignment" && Table1[week] = Current_Week))
RETURN
Current_Initial - Current_billing + Current_consignment

Finally create a measure:

Final = 
VAR Current_Week = MAX(Table1[week])
VAR Previous_Week = Current_Week - 1
VAR Current_Initial_Value = CALCULATE(MAX(Table1[Initial]), FILTER(ALL(Table1), Table1[week] = Current_Week))
VAR Previous_Mid_Step_Value = CALCULATE(MAX(Table1[Mid Step]), FILTER(ALL(Table1), Table1[week] = Previous_Week))
RETURN
Current_Initial_Value + Previous_Mid_Step_Value

The result is like below:

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.