Forum Discussion

mcc's avatar
mcc
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • mcc's avatar
    mcc
    3 years ago

    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.  

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

Difference between two rows

Column =
VAR NextIndex = Table1[Index] + 1
RETURN
    Table1[Orders]
        - CALCULATE (
            VALUES ( Table1[Orders] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex )
        )

Best Regards

mcc's avatar
mcc
Frequent Visitor
3 years ago

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.