Inserting a column to calculate the difference between values relating to new dates

Question

I am creating a table using power query, in this table are several part #'s with qty's. Every week the power query refreshes to pull in new weekly qty's for the part #'s. I am trying to create a column that calculates the differences (increase or decrease) of the qty's as they change every week.

My 3 columns are: Date, Product Number, QTY

I can't seem to find the correct syntax use for Value.Subtract([QTY], [QTY], [Date][Product Number])

Answer 1

You could do this using M (Power Query Language):

#"Sorted Rows" = Table.Sort(#"Prior Step",{{"Product Number", Order.Ascending}, {"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Added Qty Change" = Table.AddColumn(#"Added Index", "Qty Change", each try if #"Added Index"[Product Number]{[Index]-1} = [Product Number] then [Qty] - #"Added Index"[Qty]{[Index]-1} else null otherwise null, type number),
#"Removed Columns" = Table.RemoveColumns(#"Added Qty Change",{"Index"})

I think it would be more efficient to do it using DAX, in your Data Model, though. Load your table to the data model, then add a Calculated Column:

Qty Change:= 

VAR CurrentDate =  'Inventory Balance'[Date]

VAR CurrentProduct = 'Inventory Balance'[Product Number]

VAR PreviousDate = 
    CALCULATE ( 
        MAX ( 'Inventory Balance'[Date] ),
        FILTER ( 
            ALL ( 'Inventory Balance' ),
            'Inventory Balance'[Date] < CurrentDate
            && 'Inventory Balance'[Product Number] = CurrentProduct
        )
    )

VAR PreviousQty = 
    CALCULATE ( 
        SUM ( 'Inventory Balance'[Qty] ),
        FILTER ( 
            ALL ( 'Inventory Balance' ),
            'Inventory Balance'[Date]  =  PreviousDate
            && 'Inventory Balance'[Product Number] = CurrentProduct
        )
    )

RETURN
    'Inventory Balance'[Qty] - PreviousQty