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! Appreciate your Kudos Smiley Happy
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! Appreciate your Kudos Smiley Happy
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! Appreciate your Kudos Smiley Happy
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! Appreciate your Kudos Smiley Happy
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! Appreciate your Kudos Smiley Happy
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?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 397 members 4,430 guests
Please welcome our newest community members: