Datediff from nonconsecutive rows, group by category

[Fantastic_Raab]

Hello - I'm stumped finding the date difference between nonconsecutive rows, grouped by category.  ProjectID is the "category", I need to caculate the time in between the previous record and a newly created record is that has the same ProjectID.

Any help is greatly appreciated.  

[Fantastic_Raab]

Latest Attempt Using DAX:

 

My concern is the PreviousDate value isn't finding the corresponding ProjectID. 

Thanks in advance!

[selimovd]

Hey @Fantastic_Raab ,

you can either do that already in Power Query:

Value from previous row – Power Query, M language – Trainings, consultancy (exceltown.com)

Or you can do that in DAX. Ther you could use EARLIER, what is not recommended, or what I usually prefer is to do it with variables. You find both approaches in the following article:

Cracking DAX – the EARLIER and RANKX Functions - Simple Talk (red-gate.com)

Then you just have to calculate the date difference between the two values with the DATEDIFF function and you got your result.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Fantastic_Raab]

Hi Selimovd - 

Regarding the suggestion to add the previous value: I'm not sure how to grab the previous value from the same ProjectID.  And the ProjectID is not static, there will be new ones regularly.  Though I think the PowerQuery is the way to go:

I am cautoius about using the EARLIER function.  The article suggests serious lag time due to number of operations.