Get values from the previous row

Question

I'm building a Power BI report to track stock and inventory levels for various items across different locations. My data source is the item ledger entry table from Navision stored in SQL Server. This table captures item movements for each date and location.

I've imported the data using a SQL query: now I have a table where I have stock data for days where I had a movement.

Example:

Posting Date	Item No	Location Code	Quantity
2024-05-10	ITEM-A	LOC-1	10
2024-05-11	ITEM-B	LOC-2	5
2024-05-12	ITEM-A	LOC-1	-2
2024-05-14	ITEM-C	LOC-2	15

Now I would like to have stock values for the previous days. This stock value is equal to the stock value from the previous day.

Expected results:

Posting Date	Item No	Location Code	Quantity
2024-05-10	ITEM-A	LOC-1	10
2024-05-11	ITEM-A	LOC-1	10
2024-05-11	ITEM-B	LOC-2	5
2024-05-12	ITEM-A	LOC-1	-2
2024-05-12	ITEM-B	LOC-2	5
2024-05-13	ITEM-A	LOC-1	-2
2024-05-13	ITEM-B	LOC-2	5
2024-05-14	ITEM-A	LOC-1	-2
2024-05-14	ITEM-B	LOC-2	5
2024-05-14	ITEM-C	LOC-2	15

I've tried to do that with a SQL query and a cross join but it is not working (I have only date with movements and no cross join) and I do not know why. I first create a table "Dates" with all the dates from the calendar. Then I calculate the stock quantity for days I have movements in the table "dstock" and finally, I try to do a cross join between the calendar date, item_no and location to have all the combination possible and if the stock quantity is not NULL from the JOIN function, I try to take it with the LAG function from the previous row :

WITH min_max_dates AS (
    SELECT 
        MIN([Posting Date]) AS min_date,
        MAX([Posting Date]) AS max_date
    FROM [CBD$Item Ledger Entry]
),

Dates AS (
    SELECT 
        DATEADD(DAY, number, min_date) AS date
    FROM 
        min_max_dates
    JOIN 
        master..spt_values ON type = 'P'
    WHERE 
        number <= DATEDIFF(DAY, min_date, max_date)
), 

day_quantity AS (
  SELECT
    il.[Posting Date] as posting_date,
    il.[Item No_] as item_no,
    il.[Location Code] as location_code,
    SUM(il.[Quantity]) as quantity
  FROM [Axereal Hungary Kft$Item Ledger Entry] AS il
  GROUP BY il.[Posting Date], il.[Item No_], il.[Location Code]
),

dstock AS(
SELECT
    posting_date,
    item_no,
    location_code,
    SUM(quantity) OVER (PARTITION BY item_no, location_code ORDER BY posting_date 
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS stock
FROM
    day_quantity
)

SELECT
  d.date AS posting_date,
  dstock.item_no as item_no,
  dstock.location_code as location_code,
  COALESCE(dstock.stock, LAG(dstock.stock, 1) OVER (ORDER BY d.date ASC)) AS stock
FROM Dates d
CROSS JOIN (SELECT DISTINCT item_no, location_code FROM dstock) dq
LEFT JOIN dstock ON d.date = dstock.posting_date
                              AND dstock.item_no = dq.item_no
                              AND dstock.location_code = dq.location_code
ORDER BY d.date DESC, dq.location_code, dq.item_no

If SQL is not a good way to achieve what I want, is there a way to do that with a DAX function?

Answer 1

You can achieve this by some advance level transformation in Power Query.

Here is the Power Query code-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNU1NFDSUfIMcfXVdQQyfPyddQ2BNFA0VgdJkSFMkRNUkRGQNkVVY4TNIKBCFEUmMEXOSAYZAk2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Item No" = _t, #"Location Code" = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Item No", type text}, {"Location Code", type text}, {"Quantity", Int64.Type}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Item No", Order.Ascending}, {"Posting Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Posting Date", "Item No", "Location Code", "Quantity"}),
    added_min_date = Table.AddColumn(#"Reordered Columns", "min_date", each List.Min(Table.SelectRows(#"Reordered Columns",(x)=>x[Posting Date]=[Posting Date])[Posting Date])),
    added_max_date = Table.AddColumn(added_min_date, "max_date", each List.Max(#"added_min_date"[Posting Date])),
    #"Added Custom" = Table.AddColumn(added_max_date, "date_list", each List.Dates([min_date], Duration.Days([max_date] -[min_date]) +1 ,#duration(1,0,0,0) )),
    #"Expanded date_list" = Table.ExpandListColumn(#"Added Custom", "date_list"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded date_list",{"Posting Date", "min_date", "max_date"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Index", "date_list", "Item No", "Location Code", "Quantity"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"date_list", "Posting Date"}}),
    
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Item No", Order.Ascending}, {"Posting Date", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Posting Date", "Item No"}, {{"max_index", each List.Max([Index]), type number}}),


    #"Merged Queries" = Table.NestedJoin(
        #"Sorted Rows", {"Item No","Posting Date", "Index"}, 
        #"Grouped Rows", {"Item No","Posting Date", "max_index"},
        "new_table", JoinKind.Inner
    ),
    #"Expanded new_table" = Table.ExpandTableColumn(#"Merged Queries", "new_table", {"max_index"}, {"new_table.max_index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded new_table",{"new_table.max_index", "Index"}),
    #"Sorted Rows2" = Table.Sort(#"Removed Columns1",{{"Posting Date", Order.Ascending}, {"Item No", Order.Ascending}})
in
    #"Sorted Rows2"

And here is the output-

Answer 2

Another solution is to cross join using outer apply, and another outer apply to get the current or prior quantity, something like this

select c.[date],
       inv.itemno,
       inv.locationcode,
       inv.quantity
from   calendar c
  outer apply ( select inv.itemno,
                       inv.locationcode,
                       q.quantity
                from   inventory inv
                  outer apply ( select top 1
                                       q.quantity
                                from   inventory q
                                where  q.itemno = inv.itemno
                                and    q.locationcode = inv.locationcode
                                and    q.postingdate <= c.[date]
                                order by q.postingdate desc
                              ) q
                where inv.postingdate <= c.[date]
                group by inv.itemno, inv.locationcode, q.quantity
              ) inv 
order by c.[date]

You can try this yourself at this dbFiddle

Answer 3

I achieved your end result using a slightly different method. My DB Fiddle had slightly different names for the tables, so you'll have to adapt the code to your situation.

TLDR;

SELECT
  Date
  , ItemNo
  , LocationCode
  , Quantity
FROM (
  SELECT
      *
      , Max(row_num) OVER (PARTITION BY date, itemno) as last_row
  FROM (
    SELECT
        Date
        , ItemNo
        , LocationCode
        , Quantity
        , ROW_NUMBER() OVER(PARTITION BY Date, ItemNo ORDER BY PostingDate) as row_num
    FROM (
      SELECT * FROM InventoryPostings as a
      CROSS JOIN Calendar as c
      WHERE c.Date >= a.PostingDate
    ) as row_table
  ) as max_row_table
) as final_table
WHERE row_num = last_row

Explanation

• Use a cross join to bring all the dates into the table but we only keep the rows where the calendar date is higher or equal to the posting date.
• Calculate the row number of the item/date partition and order it by the real date of the record.
• We filter the result to only keep the last row from the previous step, which will be the last quantity posted before the current date.

Hope this helps