Product Movement between Racks

[este18]

Hi! I have been working with these for a few weeks and desperate for help. I want to track a product movement from inbound, transfer, to outbound. The goal is to track each back to its origin port name. The movement is recorded trough its type based on SAP.

• Inbound, movement type: 101
• Transfer between racks, movement type: 999
• Outbound, movement type: 601

Case 1 is when the product movement is direct 101 --> 999 ---> 601 which I have solved (DAX formula below). However, in case 2 (which I cannot solve), most of the time, the product transfered so many times before outbound e.g., 101 ---> 999 ---> 999 ---> 999 --> 601 and the number of its transfer varied from 2 to 8 times. 

The Data looks like this:

Date	Products ID	Quantity	Source Rack	Destination Rack	Movement Type	Movement Remark	Origin Port	End
5/16/2024	MAT123	500	Port ABC	INB1X	101	Inbound	Port ABC
5/20/2024	MAT123	500	INB1X	A006	999	Transfer	Port ABC
5/29/2024	MAT123	100	A006	CUST123	601	Outbound	Port ABC	CUST123
5/29/2024	MAT123	400	A006	CUST345	601	Outbound	Port ABC	CUST345
1/4/2024	MAT 251	800	PortAXY	INB2X	101	Inbound	PortAXY
1/4/2024	MAT 251	800	INB2X	A009	999	Transfer	PortAXY
1/10/2024	MAT 251	800	A009	A008	999	Transfer	PortAXY
3/1/2024	MAT 251	800	A008	A004	999	Transfer	PortAXY
3/2/2024	MAT 251	800	A004	CUST225	601	Outbound	PortAXY	CUST225
1/4/2024	MAT 231	700	Port AXC	INBJ2	101	Inbound	Port AXC
1/4/2024	MAT 231	700	INBJ2	A00Z	999	Transfer	Port AXC
1/10/2024	MAT 231	700	A00Z	A001	999	Transfer	Port AXC
1/12/2024	MAT 231	700	A001	CUST213	601	Outbound	Port AXC	CUST213

For case 1 when direct movement, the DAX looks like this: 

Origin Port =

VAR movementType = 'Sheet'[Movement Type] // Get the current movement type

//----------- Step 1 direct 601 --> 999 ---> 101

var var1 = CALCULATE(

        FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),

        FILTER('Sheet', 'Sheet'[Movement Type] = "999" && 'Sheet'[Destination Rack] = EARLIER('Sheet'[Source Rack])))

var var2 = IF(movementType = "601", var1, BLANK()) //return blank if movement type is not 601

var var3 = CALCULATE(

        FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),

        FILTER('Sheet', 'Sheet'[Movement Type = "101" && 'Sheet'[Destination Rack] = var2)) //use var2 as source rack

var var4 = IF(movementType = "601", var3, BLANK()) //this will result to origin port

//----------- Step 2 from transfer 999 --> 101 

var var5 = CALCULATE(

        FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),

        FILTER('Sheet', 'Sheet'[Movement Type] = "101" && 'Sheet'[Destination Rack] = EARLIER('Sheet'[Source Rack])))

var var6 = IF(movementType = "999", var5, BLANK()) //if direct, this will result to port

// Final Return Logic 601 --> 999 --> 101 going backward to get port

var final =

    SWITCH(

        TRUE(),

        movementType = "101", 'Sheet'[Source Rack], //directly use the source rack

        movementType = "601", var4,

        movementType = "999", var6,

        "Others" // Default case if none match

    )

Return final

I was thinking maybe there is some sort of loop DAX formula that can repeat searching for the transfer 999 movement type until match the other rack in the inbound 101 movement type but I am not sure how to make it with Power BI. Pls help :'))))

[Kedar_Pande]

DAX Formula

Origin Port =
VAR CurrentMovementType = 'Sheet'[Movement Type]
VAR CurrentSourceRack = 'Sheet'[Source Rack]
VAR ProductID = 'Sheet'[Products ID]
VAR FinalOriginPort = 
VAR OriginRack = 
CALCULATE(
FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),
FILTER(
'Sheet',
'Sheet'[Movement Type] = "101" &&
'Sheet'[Destination Rack] = CurrentSourceRack &&
'Sheet'[Products ID] = ProductID
)
)
VAR IsTransfer =
CALCULATE(
COUNTROWS('Sheet'),
FILTER(
'Sheet',
'Sheet'[Movement Type] = "999" &&
'Sheet'[Source Rack] = CurrentSourceRack &&
'Sheet'[Products ID] = ProductID
)
) > 0

RETURN
IF(
CurrentMovementType = "601", 
IF(NOT ISBLANK(OriginRack), OriginRack, BLANK()),
IF(
CurrentMovementType = "999" && IsTransfer,
CurrentSourceRack,
BLANK()
)
)

RETURN
IF(CurrentMovementType = "601", FinalOriginPort, BLANK())

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bhanu_gautam]

@este18 , 

using a calculated column to trace back the origin port for each outbound movement:

Create a Calculated Column to Track the Origin Port:

Origin Port =
VAR currentMovementType = 'Sheet'[Movement Type]
VAR currentSourceRack = 'Sheet'[Source Rack]
VAR currentDestinationRack = 'Sheet'[Destination Rack]

-- Function to recursively find the origin port
VAR FindOriginPort =
VAR maxIterations = 10 -- Set a reasonable limit to prevent infinite loops
VAR Loop =
GENERATE(
GENERATESERIES(1, maxIterations, 1),
VAR iteration = [Value]
VAR previousRack =
IF(iteration = 1, currentSourceRack,
CALCULATE(
FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),
FILTER('Sheet', 'Sheet'[Destination Rack] = previousRack && 'Sheet'[Movement Type] = "999")
)
)
RETURN
ROW("Iteration", iteration, "PreviousRack", previousRack)
)
VAR finalRack =
CALCULATE(
FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),
FILTER('Sheet', 'Sheet'[Destination Rack] = currentDestinationRack && 'Sheet'[Movement Type] = "101")
)
RETURN
IF(currentMovementType = "601", finalRack, BLANK())

RETURN
IF(currentMovementType = "601", FindOriginPort, BLANK())

[este18]

There is an error in the previousRack variable when it has not been defined yet.

VAR previousRack =
IF(iteration = 1, currentSourceRack,
CALCULATE(
FIRSTNONBLANK('Sheet'[Source Rack], TRUE()),
FILTER('Sheet', 'Sheet'[Destination Rack] = previousRack && 'Sheet'[Movement Type] = "999")