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

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper III
Helper III

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 ?

Highlighted
Super User IV
Super User IV

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

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

 

Highlighted
Super User IV
Super User IV

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

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

Highlighted
Super User IV
Super User IV

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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
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

Highlighted
New Member

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?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors