Tracking Logistic Movements with Power BI

[mattiadiena]

Good Morning All,

I have a table that report all the movements of materials from goods receipt to consumption.

Material are organized in handling units and they can moves from one handling unit to another one. Each movement is stored in a table and for each movement the table reports the source handling unit and the destination handling unit.

During the transformation of the table, in order to manage data with power bi, i need to add a 'Reference' column that reports the original source handling unit.

For example, for the case below (Source Handling Unit/Destination Handling Unit):

I would like to insert a dynamic column that for each row reports 1002913303 that is the original handling unit which has been moved into 1002922587.

Can you help me please?? 

Thanks and regards,

Mattia 

[dax]

Hi @mattiadiena ,

I am not clear about your requirement and your logic , if possible could you please explain it in details? Then I will try to test this in my environment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[mattiadiena]

Hi @dax ,

Thanks for your answer.

Here below you can find an example of the table i would like to create (The fourth column is what i would like to obtain).

Movement ID	Source Handling Unit	Destination HU	Original Source HU
1	2005826606	2005826606	2005826606
2	2005826606	2005826606	2005826606
3	2005826606	2005826606	2005826606
4	2005826606	2005845174	2005826606
5	2005845174	2005845174	2005826606
6	2005845174	2005845174	2005826606
7	2005845174	0	2005826606
8	2005845174	0	2005826606
9	2005845174	0	2005826606
11	2005845174	0	2005826606
13	2005826608	2005826608	2005826608
14	2005826608	2005826608	2005826608
15	2005826608	2005826608	2005826608
16	2005826608	2005826608	2005826608
17	2005826608	2005826608	2005826608
18	2005826608	0	2005826608
19	2005826608	0	2005826608
20	2005826608	0	2005826608
26	2005826610	2005826610	2005826610
27	2005826610	2005826610	2005826610
28	2005826610	2005826610	2005826610
29	2005826610	2005853372	2005826610
30	2005853372	2005853372	2005826610
31	2005853372	2005853372	2005826610
32	2005853372	0	2005826610
33	2005853372	0	2005826610
34	2005853372	0	2005826610
35	2005853372	0	2005826610
36	2005853372	0	2005826610
37	2005853372	0	2005826610
38	2005853372	0	2005826610
39	2005853372	0	2005826610
40	2005853372	0	2005826610
41	2005853372	0	2005826610
42	2005853372	0	2005826610
43	2005853372	0	2005826610

Each row refers to a specific movement, which is characterized by a source Handling Unit and a destination Handling Unit.

There is always an unique original source handling unit and the movement can be beetween the same handling unit or to new handling units. Through i would like to insert a calculated column that, as the last column proposed above, reports for each row the original source handling unit number of that specific movement.

For  example in the table above you can notice that the first three rows refers to movements beetween the handling unit 2005826606 and itself, for all rows the original source is 2005826606.

The fourth row reports a movement from 2005826606 to 2005845174 (New Hansdling Unit), the original source remains 2005826606.

From the fourth row the table report movents from handling unit 2005845174 to itself or to other destination, also for these cases the original source remains 2005826606.

I need to insert the a column that, by analizing the movements contained in the table, automatically defines for each row the original source handling unit.

In this way i could better analyze data and delete all the rows that are consequence of other handling unit.

I hope you can help me.

Thank you very much,

Mattia

[ToddChitt]

The question makes sense. Something moves from A to B to C to D, and all along the way, you want each 'movement' to be able to report that A was the starting point of this journey.

Questions: Is each Movement related to a Material ID or some other identifier? Also, is there something in the Movement table to indicate the ORDER in which they happened? Maybe a Date/Time stamp, or Movement ID?

Assuming both answers are YES, then you should be able to do the following:

* Duplicate your Movement table.

* Do a Group By and get the FIRST Movement for each unique Material ID

* Join that to the original Movement table (MERGE) on Material ID and Movement Date (or Sequence or whatever) to get the Source Handling Unit.

* Back on your original query, MERGE it with the result from above on only Material ID, forget about the Date or Sequence, and pick up the Source Unit.

Hope this helps.

[mattiadiena]

Hi @ToddChitt,

Thank you very much for your support.

I understood your proposed solution, but unfortunately it doesn't work.

In my table unfortunately i don't have an unit key that could be used like a foreign key for the other rows.

Let me explain:

When a material ID is received it could be splitted in more source handling units and from that it could go everywhere, so for each source handlign unit i need to track every step.

Then for example i could have:

Goods Receipt of material ID1 that is splitted in handling units (Three rows): 

a1

a2

a3

Then the handling unit a1 moves to handling unit b1; the handling unit a2 moves to handling unit b2 and the handling unit a3 remains itself. So i will have 5 rows (The first three are the goods receipt movements).

GR: a1->a1

GR: a2->a2

GR a3->a3

M1: a1->b1

M2: a2->b2

For every movements the material ID is the same and also the goods receipt document (except for the handling unit, i don't have other possible keys).

After the movements above, the system reports other movements, e.g. the handling unit b1 moves to handling unit b3:

M3: b1->b3

And i need to know that this movement is consequence of source handling unit a1.

Maybe with a duplicate table i can loop many times into the duplicated table by access with the source handling unit (in this case b1) until i found the original source, but it seems a little time consuming with many rows.

Anybody knows other ways?

Thanks and regards,

Mattia 

[ToddChitt]

@mattiadiena 

Sorry to say, but without some indentifying key that represents the thing that is getting moved, be it an original receipt of goods or a subset thereof, you don't have much to go on. Consider the following two rows:

From  .....  To

Unit A      Unit B

Unit C      Unit B

If you try to trace the path backwards based on this data, it will indicate that 'something' came from TWO different sources. In reality that won't be the case. In reality, that would be two different 'things' that moved. Unless you can identify exactly what those two things are, my method won't work. So maybe I missunderstood your data model.

Can a Handling Unit handle more than one piece of material? 

What is a "Handling Unit" Is it a station of some sort along a process line? Maybe if you described the process in terms of your industry and not 'database speak' I might have a better chance.

Thanks.

[mattiadiena]

Hi @ToddChitt ,

We can define an Handling Unit as a grouping of quantities of a spefic material, that optimizes the material movements within a logistic warehouse.

For example let assume that the material code A is grouped in handling unit of 100 pcs each.

When we receive 1000 pcs of material A, the system automatically 10 Handling units for that material and It records ten movement's rows.

1000 pcs of material A are divided in 

100 pcs for the handling unit SC1

100 pcs for the handling unit SC2

...

100 pcs for the handling unit SC10

The ten handling units the system created before can be moved to themself or to other new handling units, so for example, from the first handling unit (SC1) we can record a movement of 50 pcs to another new handling unit. The movements are created by the warehouse users that can decide to store directly the handling unit or to make them more comfortable for the warehouse activities, by dividing them in other handling unit with smaller quantities.

Then for example:

The user decide to move 50 pcs from SC1 to a new Handling Unit SC11

In this way he created a new handling unit (SC11) with a subquantity of the first handling unit and he left 50 pcs in the first handling unit (SC1), in order to make easier the activities of warehouse storage.

However it is not possibile that two different handling unit move to the same new handling unit, so as you wrote it is not possible that also A and C moves to B.

From  .....  To

Unit A      Unit B

Unit C      Unit B

For every movement of sub-quantities of the first handling unit the system automatically creates a new handling unit, where that specific quantity goes, as i explain before.

That problem is that in power bi i should be able to identify that a movement is consequence of another original movement (and an original handling unit source), that one should be my table key.

I hope to have made more clear my requirements, but hower i admire so much your support.

Thanks,

Mattia

[mahoneypat · Accepted solution]

This could be a good application of the PATH function on the DAX side; however, I suspect you'd have to do some additional data prep to make it work (it can be finicky).  The PATH function is handy for reporting relationships and genealogy applications like this.  There is also an M path version of PATH found here (but it doesn't scale well in my experience) - 

https://docs.microsoft.com/en-us/dax/path-function-dax

https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-s-PATH-function-equivalent-Custom-Column...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

You should be able to do that in Power Query. What have you tried so far?