Forum Discussion

AlphaEcho85's avatar
AlphaEcho85
Frequent Visitor
2 years ago
Solved

Help needed with transforming stock table in database

So im working with a database belonging to a company my employer just aquired,

The database has a table with all in and out transactions i.e. sales, purchase to stock , product conversions and internal movements between locations/warehouses.

 

The strange thing with the database is that when they do a physical stock count the result of the stock count is plugged in as a total number, and not the delta between all previous transactions and the result of the stock count. (which im used to)

 

This means that I need to, in some way, "reset" the counter everytime there is a stock count done.

 

What I dont get my head around is how to approach resetting:

 

I can get it done heavliy manually for one product on one warehouse, but when i start to involve several products and warehouses my logic breaks.

 

My dream-goal is to generate a fact table in power query that contains all actual in and out transactions and a delta result of the stock count, instead of the actual quantity of the stock count. This way i can summarize the fact table with a running total measure on any given date to get a balance per product and warehouse

 

Any help is highly appreciated!!

 

The example data below show how the data looks in the database.

  1. There are not daily data, only when transactions happen.
  2. Most products do not have a stock count way after they appear in the warehouse (stock count is done yearly at a minimum, i.e. if a product is introduced in february, there might be no stock count in 10 months +/- i.e. no stock count to "reset" the counter)

 

RowIDDateProductNumberMovementTypeQuantityWarehouse
101.01.2024APurchase251
202.01.2024ASales-51
303.01.2024ASales-21
405.01.2024ASales-21
506.01.2024AStock Count141
607.01.2024APurchase101
701.01.2024BStock Count1052
802.01.2024BPurchase102
903.01.2024BSales-1002
1005.01.2024BInternal Transfer-102
1105.01.2024BInternal Transfer101
1206.01.2024BStock Count32
1306.01.2024BStock Count101
1407.01.2024

B

Purchase102
1508.01.2024

A

Product Conversion-11
1608.01.2024

A1

Product Conversion13

 

  • If you ran a report on Product A warehouse 1 on 04.01.2024 the balance should be 18, but if you ran the same report on 07.01.2024 it should return 24

 

  • Product B warehouse 2 on 05.01.2024 shoud return 5 and on 06.01.2024 return 3
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, AlphaEcho85 
    Thanks for the reply from amitchandak , please allow me to provide another insight:

    You can try the following M expression in Power Query:

    let
    Source = YourDataSource, 
    ChangedType = Table.TransformColumnTypes(Source,{{"Quantity", type number}}),
    AddedCustom = Table.AddColumn(ChangedType, "Adjusted Quantity", each if [MovementType] = "Stock Count" then [Quantity] - List.Sum(Table.SelectRows(ChangedType, (inner) => inner[ProductNumber] = [ProductNumber] and inner[Warehouse] = [Warehouse] and inner[Date] < [Date])[Quantity]) else [Quantity]),
    SortedRows = Table.Sort(AddedCustom,{{"Date", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1),
    AddedRunningTotal = Table.AddColumn(AddedIndex, "Running Total", each List.Sum(Table.SelectRows(AddedIndex, (inner) => inner[ProductNumber] = [ProductNumber] and inner[Warehouse] = [Warehouse] and inner[Index] <= [Index])[Adjusted Quantity]))
in
    AddedRunningTotal

     

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    AlphaEcho85 , I think you need build inventory using the running total, with help from date table joined with date of your table

     

    Inventory / OnHand =

    CALCULATE(SUM(Table[Quantity]),filter(all(date),date[date] <min(date[date])), filter(Table, Table[MovementType] in {"Purchased", "Internal Conversion"}) -
    CALCULATE(SUM(Table[Quantity]),filter(all(date),date[date] <min(date[date])), filter(Table, Table[MovementType] in {"Sales"})

     

    Correct Movement type as per need

     

    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, AlphaEcho85 
    Thanks for the reply from amitchandak , please allow me to provide another insight:

    You can try the following M expression in Power Query:

    let
    Source = YourDataSource, 
    ChangedType = Table.TransformColumnTypes(Source,{{"Quantity", type number}}),
    AddedCustom = Table.AddColumn(ChangedType, "Adjusted Quantity", each if [MovementType] = "Stock Count" then [Quantity] - List.Sum(Table.SelectRows(ChangedType, (inner) => inner[ProductNumber] = [ProductNumber] and inner[Warehouse] = [Warehouse] and inner[Date] < [Date])[Quantity]) else [Quantity]),
    SortedRows = Table.Sort(AddedCustom,{{"Date", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1),
    AddedRunningTotal = Table.AddColumn(AddedIndex, "Running Total", each List.Sum(Table.SelectRows(AddedIndex, (inner) => inner[ProductNumber] = [ProductNumber] and inner[Warehouse] = [Warehouse] and inner[Index] <= [Index])[Adjusted Quantity]))
in
    AddedRunningTotal

     

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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