Approach for calculating differences between rows based on conditions (Excel arrays to PowerBI)

[ivdzouwen]

Goodday all, 

I hope somebody can help me with a problem I'm having. Hopefully I can one day return the favor.

I have big dataset that I used to prepair and anlyse in excel by calculating a number of array index match functions. I like to bring this to PowerBI, I tried to transform and simulate my fucntions in DAX but with no succes. The table below illustrates my problem. 

In a nutshell

We have a warehouse with batches containing multiple jobs. Each job gets the same processing steps during which a measurements is done. The most interresting information is the difference in time and measurement between consequative steps. For example, to show the measurement difference tween step 3 and 4 over time. I used to use arrays to find the previous step of the job ID and than calculated diffrence since last step in column CALC. I wonder however even if trying to recreate this in PowerBI is the right approach

*the combination of Batchh ID, Job ID and step is unique

*Steps are consequative in time

batch ID	Job ID	Step	DateTimestamp	Measurement value	CALC
11	222	3	..	##
11	222	4	..	##
11	222	5	..	##
11	333	4	..	##
11	333	5	..	##
11	333	6	..	##
22	555	12	..	##
22	666	2	..	##
22	666	3	..	##

Hope this is clear and I handeld asking it like this correctly.  Kind regards, Ivo

[Mariusz · Accepted solution]

Hi @ivdzouwen 

All you need to do is add a custom column = step  +1 to your table and later use Merge Queries.

Please see the attached, this will show only technique as the sample had not enough data return any meaningful result.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[ivdzouwen]

Hello Mariusz,

wow, that looks so simple. Thank you very much. this will work for me. kind regards.

Hope to be able to help people in the future. kind regards.

I