Forum Discussion
Datediff ROWS - 1 order, 8 steps. 3 critical columns: order #, date/time, step name
This data is interfaced with an app and is returned as the below:
Woe_orderno is the order #. I have over 20,000 orders. I want to build a matrix that shows each order and the time it took for each step in the StampType column. StampType column is a related table based on the woe_activitytype.
I’m trying to show the time it took from Start Pump to Stop Pump, then Stop Pump to Call Surveyor, etc.
I have tried unpivoting woe_activitytype and Start Time and it just returns a new column with attributes and the same data format.
What am I missing?
This is resolved. It was correct to pivot, but I was "unpivoting" when I should have first "pivoted" then "unpivoted." That solved my layout and I could write the measures for the datedif (as they were not in sequence). Thank you all.
4 Replies
Hi mcc ,
In order to give you a suitable solution quickly, could you please provide some raw data(exclude sensitive data) in the order table with Text format? And share the order of every steps? What's your expected result? Do you want to get the time duration for every step in Power Query? If yes, you can refer the following link to get it...
1. Start Pump
2. Stop Pump
3. Call Surveyor
...
time difference between 2 rows
In addition, we can consider to achieve it by DAX. Please refer the following link to get it.
Calculate the difference between 2 rows in PowerBI using DAX
Find difference between two rows by usind Dax in Power BI
Column = VAR NextIndex = Table1[Index] + 1 RETURN Table1[Orders] - CALCULATE ( VALUES ( Table1[Orders] ), FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex ) )Best Regards
Thank you for offering your suggestion. It is a very good one; however, the times stamps are not in sequence. I did resolve the issue though...I was "unpivoting" when I should have first "pivoted" then "unpivoted." That solved my layout and I could write the measures for the datedif (as they were not in sequence). Thank you all.
Thank you for information. I should note, the calculations I need are not insequence. So, index will not work, as I am not using one stamp to the next. It is specific stamps. The expected results is that I can create any time difference between any 2 stamps. But they are not in sequence. It can be the 1st and 5th stamps or the 2nd and 6th. Every variation.
This is resolved. It was correct to pivot, but I was "unpivoting" when I should have first "pivoted" then "unpivoted." That solved my layout and I could write the measures for the datedif (as they were not in sequence). Thank you all.
