Forum Discussion
Finding days between two rows date values
This is kind of complex and I haven't been able to figure it out. I am trying to create a column that finds the number of days between a project status but the project ID must be the same. The project Establish date is held in a a project table.
Project status starts at 1.0 and goes up to 4.0. I am able to calculate the number of days it took the project to get to the status it is at from when the project was created. I want to find the amount of time it may take to get from status 3.0 to 4.0 and so on.
| ID | Project ID | Modified date | Status | Days from Project Established date | Days between Status (Column I am trying to create) |
| 1 | 11362 | 1/20/20 | 1.0 | 0 | 0 |
| 2 | 11362 | 1/23/20 | 2.0 | 3 | 3 |
| 3 | 14598 | 1/25/20 | 3.0 | 15 | 5 |
| 4 | 11362 | 1/30/20 | 3.0 | 10 | 7 |
| 5 | 14598 | 1/30/20 | 4.0 | 25 | 10 |
Hi ConnorH ,
Create a Calculated Column
Column = VAR _prevstage_sameprojId_date = CALCULATE ( MAX ( 'Table1'[Modified date] ), FILTER ( 'Table1', 'Table1'[Modified date] < EARLIER ( 'Table1'[Modified date] ) && Table1[Project ID] = EARLIER ( Table1[Project ID] ) ) ) RETURN DATEDIFF ( _prevstage_sameprojId_date, 'Table1'[Modified date], DAY )Regards,
Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
6 Replies
Hi ConnorH ,
Create a Calculated Column
Column = VAR _prevstage_sameprojId_date = CALCULATE ( MAX ( 'Table1'[Modified date] ), FILTER ( 'Table1', 'Table1'[Modified date] < EARLIER ( 'Table1'[Modified date] ) && Table1[Project ID] = EARLIER ( Table1[Project ID] ) ) ) RETURN DATEDIFF ( _prevstage_sameprojId_date, 'Table1'[Modified date], DAY )Regards,
Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!I have a similar instance where I would really aprecaite your help.
I am trying to create a column that finds the number of workingdays (excluding weekends and bankholidays - I have a list of all bank holidays in a table) between Milestones but the Audit Review ID must be the same. I will need to calculate the difference between the End Day of Milestone 1 with the end date with Milestone 2 and so on... Not all Audit Reviews have an End Date for all 7 milestones, some only have a few as seen in example.
Audit Review ID Milestone Type Milestone End Date 123456 1. Background Research 01/12/2021 123456 2. Terms of Reference 03/02/2022 123456 3. Opening Meeting 04/03/2022 123456 4. Fieldwork and Closing Meeting 21/03/2022 123456 5. Draft Report 25/03/2022 123456 6. Management Responses 29/03/2022 123456 7. Final Report 07/04/2021 234567 1. Background Research 01/01/2022 234567 2. Terms of Reference 03/01/2022 234567 3. Opening Meeting 04/02/2022 234567 4. Fieldwork and Closing Meeting 12/02/2022 345678 1. Background Research 01/01/2021 345678 2. Terms of Reference 23/01/2022 For example, I would expect a value of 5 for the difference between (4. Fieldwork & Closing Meeting) and (5. Draft Report) for Audit 123456.
Would apprecaite your help.
I used this to calculate Days Between Loads and was wondering how I could add to it to also calculate if it was the last Load, then how many days since then to "today"?
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
ConnorH , Try a new column like
datediff(Table[Date], minx(filter(Table,Table[Date] <earlier(Table[Date])) ,Table[Date]),day)
