cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: DATEDIFF between Rows and GROUP BY

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)




Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Super User
Super User

Re: DATEDIFF between Rows and GROUP BY

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

m.PNG





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Giavo Regular Visitor
Regular Visitor

Re: DATEDIFF between Rows and GROUP BY

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

Super User
Super User

Re: DATEDIFF between Rows and GROUP BY

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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Giavo Regular Visitor
Regular Visitor

Re: DATEDIFF between Rows and GROUP BY

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

 

Super User
Super User

Re: DATEDIFF between Rows and GROUP BY

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

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





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Giavo Regular Visitor
Regular Visitor

Re: DATEDIFF between Rows and GROUP BY

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

Super User
Super User

Re: DATEDIFF between Rows and GROUP BY

@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. 





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Menolly Frequent Visitor
Frequent Visitor

Re: DATEDIFF between Rows and GROUP BY

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

benario Frequent Visitor
Frequent Visitor

Re: DATEDIFF between Rows and GROUP BY

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?