Forum Discussion
Calculate DateDeff Between subsequent rows pending Sorting / Filters - Power Pivot
Hello,
New poster here but I have done some extensive research for a couple days now and having a hard time developing the DAX formula for calculating the time difference between one row and the subsequent rows. I was using some of the content from this post to get a basic understanding but my issue is a bit more complicated:
I have order numbers
- These have many operations
- These have many sub-operations
I want to filter on orders, sort on operations (and sub-operations), and determine the time difference from one sub-operation to the next. (Granted my real data set has 1000s of orders, some having 30 operations, and having 10 sub-operations for each operation). I can do this via VBA no problem, but was hoping on a Power BI / Power Pivot solution
Here is a Sample Data Set:
| Order Number | Operation Number | Sub-Operation Number | Time Stamp | Results |
| 1 | 10 | 10 | 2020-02-23 | null |
| 1 | 10 | 20 | 2020-02-24 | 1 |
| 1 | 20 | 10 | 2020-02-26 | 2 |
| 2 | 10 | 10 | 2020-01-30 | null |
| 2 | 20 | 10 | 2020-02-03 | 4 |
| 2 | 20 | 20 | 2020-02-04 | 1 |
Let me know what you think.
Try
date diff = datediff(table[timestamp] , maxx(filter(table,table[Order Number]=earlier(table[Order Number]) && table[timestamp]< earlier(table[timestamp])),table[timestamp]),DAY)
2 Replies
Try
date diff = datediff(table[timestamp] , maxx(filter(table,table[Order Number]=earlier(table[Order Number]) && table[timestamp]< earlier(table[timestamp])),table[timestamp]),DAY)Excellent Work.
It seems to work quite well. I have adjusted it to give me decimal numbers.
Thanks for the quick response!
