DATEDIFF between Rows and GROUP BY

[Giavo]

Hello everybody. 2 DAX Questions about DATEDIFF.

1)I need to find the difference in minutes between the rows of "DateTime" column

2)I need to find the difference in minutes between the rows of "DateTime" column GROUPING BY "IWC Number"

Can you please help me with DAX ?

[Myagi-Do]

This is not helpful at all. Who approved this?

[Phil_Seamark]

HI @Giavo

When you group by IWC number, what dates do you want to compare?

[Giavo]

after having the first formula (datediff between row and row+1), i would like to see if i can sum this Minutes(datediff) for each "IWC Number". thanks

[parry2k]

do you mean difference between row and (row -1), not row + 1

[Giavo]

yes you're right. Row and Row - 1

[parry2k]

@Giavo 

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!

[Giavo]

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

[parry2k]

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)

[benario]

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?

[Giavo]

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

[parry2k]

@Giavo seems like your rows contains the data where current row date/time value is less than previous row date/time value? Do you think that is possible, if yes then need to tweak the datediff formula. 

[parry2k · Accepted solution]

here is the output i see on my end from your data

[benario]

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 ?

[Giavo]

Thank you very much, really great job. Would it be possible a new Column instead of a Measure ?

[parry2k]

unfortunately not in the same table, we have to create a calculated table.

[Menolly]

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

[Giavo]

oh ok, it is ok then. My next step would be to SUM this minutes per each IWC Number

[parry2k]

summ of minutes = sumx(x, [Minute Diff])

add above measure, drop table visual with iwc number and above measure, you will have it. 

[Giavo]

Isn't it the same thing? 

[parry2k]

when you said difference, you mean difference in minute from current row datetime to previous row datetime