Forum Discussion
DATEDIFF between Rows and GROUP BY
here is the output i see on my end from your data
Giavo :manhappy:
Can you paste the data so that i can use that for calc instead of the image you pasted, or share excel file (using google drive or other means). cheers!
yes sure, thank you for helping
| ProgressiveIndex | IWC Number | DateTime | Status |
| 1 | 100 | 13/10/17 7:10 | 1 |
| 2 | 100 | 13/10/17 9:04 | 3 |
| 3 | 100 | 13/10/17 9:10 | 3 |
| 1 | 103 | 13/10/17 9:18 | 2 |
| 2 | 103 | 14/10/17 9:21 | 5 |
| 3 | 103 | 14/10/17 9:31 | 7 |
| 1 | 106 | 14/10/17 9:50 | 31 |
| 2 | 106 | 14/10/17 10:09 | 21 |
| 3 | 106 | 14/10/17 11:02 | 43 |
try this, in query editor, add a index column , then add following measure
Minute Diff = var prevRowDate = CALCULATE(MAX(x[DateTime]), Filter(ALLSELECTED(x), x[Index] < MAX( x[Index]))) return datediff(prevRowDate, max(x[DateTime]), MINUTE)
here is the output i see on my end from your data
Thank you very much, really great job. Would it be possible a new Column instead of a Measure ?
Hi parry2k, thank you again for your help. Unfortunately it didn't work today on my project at work because it says that the Start date can not be after the End date
hey, I have understood that the x in x[index] is the table name. However every time i run that same formula ti get the message that the the syntax for '[index]' is incorrect. how do i correct it?
