cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fantastic_Raab
Regular Visitor

Datediff from nonconsecutive rows, group by category

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.  

2021-06-14 16_35_30-Window.png

3 REPLIES 3
Fantastic_Raab
Regular Visitor

Latest Attempt Using DAX:

 

 2021-06-15 11_46_34-Window.png

 

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

 

Thanks in advance!

selimovd
Super User
Super User

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
 

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:

2021-06-14 18_33_21-Window.png

 

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors