Forum Discussion

Matt_JEM's avatar
Matt_JEM
Icon for Helper I rankHelper I
1 year ago
Solved

Tracking stock in different warehouses

Good day Everybody.   I am new to Power Bi and would appreciate your help.   We have stockcodes for each project that move to different locations (Warehouse) in the factory. Each time a stockcode...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi all,thanks for the quick reply, I'll add more.

    Hi Matt_JEM ,

    Regarding your question, your raw data appears to be sorted in chronological order. My idea would be to group based on the column 'Stock code' column and then create index columns. The largest row of the index column is the final Warehouse.

    Please follow these steps:

    1.

    2.

    Table.AddIndexColumn([Summarize],"Index",1)

    3.

    Complete m code

    let
    Source = yourfilepath,
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Trn Qty"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"StockCode"}, {{"Summarize", each _, type table [StockCode=text, Warehouse=text, NewWarehouse=nullable text, EntryDate=text, TrnTime=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Summarize],"Index",1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"StockCode", "Summarize"}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns1", "Index", {"StockCode", "Warehouse", "NewWarehouse", "EntryDate", "TrnTime", "Index"}, {"StockCode", "Warehouse", "NewWarehouse", "EntryDate", "TrnTime", "Index.1"})
in
    #"Expanded Index"

    4.Use the following DAX expression to create a measure

    Measure = 
VAR _endWarehouse = CALCULATE(MAX('Table'[Index.1]),ALL('Table'[Warehouse]))
VAR _currentWarehouse = MAX('Table'[Index.1])
VAR _result = IF(_currentWarehouse = _endWarehouse," ")
RETURN _result

    5.Final output

     

    Best Regards,
    Wenbin Zhou

Matt_JEM's avatar
Matt_JEM
Icon for Helper I rankHelper I
1 year ago

If I select a warehose, that warehouse still list the stockcodes that have already moved out of the warehouse

BugmanJ's avatar
BugmanJ
Icon for Helper V rankHelper V
1 year ago

Apologies I missed that you wanted a slicer involved.

 

Doing this from memory but you need to adjust the filter in the 2nd measure so that it filters by warehouse too.

 

i can't see what the column is called as replying without my pc but try this, adjusting the warehouse column name to suit.

 

2)Latest Warehouse Location =

VAR LatestDateTime =
    [Latest DateTime]
RETURN
    CALCULATE(
        MAX('Table'[Warehouse]),
        'Table'[DateTime] = LatestDateTime,
        ALLEXCEPT('Table', 'Table'[StockCode], 'Table'[Warehouse])
    )

 

 

  • Matt_JEM's avatar
    Matt_JEM
    Icon for Helper I rankHelper I
    1 year ago

    Good day BugmanJ 

     

    Thank you for trying to help. Something is still not working correctly.The stockcode SAA001-JTJ0135-01 is no longer in warehouse JEMP05. I have another board that show the movement per stock code in time.

    From the above graph I can see that my data is correct and that the stockcode moved from warehose JEMP09 to warehouse JEMR02 on 31/10/24 at 10:29. According to the top picture where I have selected the JEMP05 slicer stock code SAA001-JTJ0135-01 is still in warehouse JEMP05.

     

    I thank you in advance for your patience and time.

     

    Regards 

     

    Matt

  • Matt_JEM's avatar
    Matt_JEM
    Icon for Helper I rankHelper I
    1 year ago

    Hi BugmanJ.

     

    I found the problem but don't know how to solve this. The time that a action is recordered is Syspro ERP is 11:05:55:28. Power Bi does not recognise this as a correct time. That means that PowerBi see 11:35:50:19 and 11:35:50:26 as the same time 11:35:50. Therefore it can not determine which of the two transactions below is the absolute last and therefor list the Latest Warehouse Location as JEMP02 and JEMP09. The Latest Warehouse location should only be JEMP09 because 11:35:50:26 is the actual absolute last tansaction. 

    I have tried create a Latest Date Time coloumn but I get an error as soon as I use TrnTime. I have add an extra colomn names SS to list the last 2 digits of TrnTime. How do I now select the lasted date and time with the highest value of SS?

     

    I thank you in advance for your time.

     

    Regards

     

    Matt

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi all,thanks for the quick reply, I'll add more.

      Hi Matt_JEM ,

      Regarding your question, your raw data appears to be sorted in chronological order. My idea would be to group based on the column 'Stock code' column and then create index columns. The largest row of the index column is the final Warehouse.

      Please follow these steps:

      1.

      2.

      Table.AddIndexColumn([Summarize],"Index",1)

      3.

      Complete m code

      let
    Source = yourfilepath,
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Trn Qty"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"StockCode"}, {{"Summarize", each _, type table [StockCode=text, Warehouse=text, NewWarehouse=nullable text, EntryDate=text, TrnTime=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Summarize],"Index",1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"StockCode", "Summarize"}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns1", "Index", {"StockCode", "Warehouse", "NewWarehouse", "EntryDate", "TrnTime", "Index"}, {"StockCode", "Warehouse", "NewWarehouse", "EntryDate", "TrnTime", "Index.1"})
in
    #"Expanded Index"

      4.Use the following DAX expression to create a measure

      Measure = 
VAR _endWarehouse = CALCULATE(MAX('Table'[Index.1]),ALL('Table'[Warehouse]))
VAR _currentWarehouse = MAX('Table'[Index.1])
VAR _result = IF(_currentWarehouse = _endWarehouse," ")
RETURN _result

      5.Final output

       

      Best Regards,
      Wenbin Zhou