Finding days between two rows date values

[ConnorH]

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

[harshnathani · Accepted solution]

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 Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[young351]

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"?

[GeorgeR1]

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.

[lipsary]

@GeorgeR1  : I have similiar kind of scenario. By any chance if you find any solution . Kindly, can you please post here.

[amitchandak]

@ConnorH , Try a new column like

datediff(Table[Date], minx(filter(Table,Table[Date] <earlier(Table[Date])) ,Table[Date]),day)

[Greg_Deckler]

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/3395...