Step by step process - Backward tracing of original document

[Rate]

Hello!!

I am struggling greatly with an iterative calculation, and I want to ask you guys for some help, I am horribly stuck. 

In our company we have a production process that has several steps, where we start by inputing original cocoa and go through several processes until it is ready for sale. We want to be able to link every step in the process with the original document (invoice) for the cocao bought to the Producer. For this, we have a table with input and output for each of these steps, where each process have a unique id_process (common for input and output of each particular process) and an id_input and id_output for each line, so that when a process finishes it generates an id_output that might be used in another process as id_input.

What i would love to achieve is being able to trace back every process to its original document (marked as "Original document" in the example table below, that I completed for better explanation), and, if possible, to count the number of steps from the original input to the final one, (the column I named stage, also completed for the example).

Do you have any idea as to how I could work towards this?

document	Process	id_Input	id_Output	Weight Input	Weight Output	id_process	Original document	Stage
04-002627.	Input	21849		3		6028	04-002627.	1
	Output		5051		1371	6028	04-002627.	1
	Input	5051		1371		7412	04-002627.	2
	Output		6575		521	7412	04-002627.	2
04-002627.	Input	21849		692		6514	04-002627.	1
	Output		5898		646	6514	04-002627.	1
	Input	5898		646		9684	04-002627.	2
	Output		9727		223	9684	04-002627.	2
	Input	9727		223		10030	04-002627.	3
	Output		15998		200	10030	04-002627.	3
04-002627.	Input	21849		708		6515	04-002627.	1
	Output		5899		656	6515	04-002627.	1
	Input	5899		656		6723	04-002627.	2
	Output		9721		454	6723	04-002627.	2

I realy appreciate your help!

Kind regads, 

Rodrigo.

I have 

[Anonymous · Accepted solution]

Hi @Rate ,

First create an index column:

Go to query editor>add column>index column;

Then create 3 calculated column as below:

Original document = 
var _doc=CALCULATE(MAX('Table'[document]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])&&'Table'[document]<>BLANK()))
Return
IF('Table'[document]<>BLANK(),'Table'[document],_doc)

Group = 
var _nextdoc=CALCULATE(MAX('Table'[document]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
Return
IF('Table'[document]<>BLANK(),'Table'[Index],CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])&&'Table'[document]<>BLANK())))

Stage = RANKX(FILTER('Table','Table'[Group]=EARLIER('Table'[Group])&&'Table'[id_Input]=BLANK()),'Table'[Index],,ASC,Dense)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Rate]

Hello @Anonymous ,

That worked magic. NICEEE!

Thank you so much! I am really grateful!!

[kentyler]

I think you have an implicit id that you probably need to add. I've called it "Cycle ID"
document	Cycle ID	Stage	Process	id_Input	id_Output	Weight Input	Weight Output	id_process	Original document
04-002627.	1	1	Input	21849		3		6028	04-002627.
	1	1	Output		5051		1371	6028	04-002627.
	1	2	Input	5051		1371		7412	04-002627.
	1	2	Output		6575		521	7412	04-002627.
04-002627.	2	1	Input	21849		692		6514	04-002627.
	2	1	Output		5898		646	6514	04-002627.
	2	2	Input	5898		646		9684	04-002627.
	2	2	Output		9727		223	9684	04-002627.
	2	3	Input	9727		223		10030	04-002627.
	2	3	Output		15998		200	10030	04-002627.
04-002627.	3	1	Input	21849		708		6515	04-002627.
	3	1	Output		5899		656	6515	04-002627.
	3	2	Input	5899		656		6723	04-002627.
	3	2	Output		9721		454	6723	04-002627.
The next step is to combine the input/output columns and the weight columns. Each row knows whether it is an input or an output and having the values you want to compare in different columns will make the comparison harder.
And then we add an "index" so that we can figure out what the first step and the last step for a particular cycle are.
document	Index	Cycle ID	Stage	Process	Process_Id	Weight	id_process	Original document
04-002627.	1	1	1	Input	21849	3	6028	04-002627.
	2	1	1	Output	5051	1371	6028	04-002627.
	3	1	2	Input	5051	1371	7412	04-002627.
	4	1	2	Output	6575	521	7412	04-002627.
04-002627.	5	2	1	Input	21849	692	6514	04-002627.
	6	2	1	Output	5898	646	6514	04-002627.
	7	2	2	Input	5898	646	9684	04-002627.
	8	2	2	Output	9727	223	9684	04-002627.
	9	2	3	Input	9727	223	10030	04-002627.
	10	2	3	Output	15998	200	10030	04-002627.
04-002627.	11	3	1	Input	21849	708	6515	04-002627.
	12	3	1	Output	5899	656	6515	04-002627.
	13	3	2	Input	5899	656	6723	04-002627.
	14	3	2	Output	9721	454	6723	04-002627.
I'm just guessing, of course. If want to do a screen share and dive deeper feel free to email me ken at 8thfold dot com and we can set up a zoom meeting.
thanks for a great question!