Forum Discussion
DATEDIFF between Rows and GROUP BY
here is the output i see on my end from your data
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 ?
unfortunately not in the same table, we have to create a calculated table.
oh ok, it is ok then. My next step would be to SUM this minutes per each IWC Number
summ of minutes = sumx(x, [Minute Diff])
add above measure, drop table visual with iwc number and above measure, you will have it.
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
Hi,
I'll be really interested by how you create this new table from the date column and the measure.
I tried many ways but I'm not satisfied by result.
Thanks
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?
Kindly help me understand the formula because i keep getting the erro message that the syntax for '[index]' is incorrect. How is the [index] section of the formula supposed to posted ?
