Date Difference Between two columns and rows

[michael_knight]

Hi,

I'm trying to create a formula which will display the difference between The End Date and the Start Date. This is showing how long it took to start a process after finishing one. In the case for ID 1, they finished the process and then started another one, it took 23 days to start the next process

ID	Start Date	End Date	Expected Outcome
1	05/05/2021	23/05/2021	0
1	15/06/2021		23
2	25/03/2021		0
3	02/07/2021	06/07/2021	0
3	17/07/2021	20/07/2021	11
3	29/07/2021		9
4	09/09/2021		0
5	25/09/2021	27/09/2021	0
5	15/10/2021	20/10/2021	18

The Expected Outcome is what I manually did, I want to create that column within Power BI

I posted the table because I'm to upload to OneDrive etc. 

Thanks,

Mike

[michael_knight]

Hi,

Does anyone know how I can fix the issue above?

Thanks

[amitchandak]

@michael_knight , Try a new column like

Column = var _max1 = maxx(FILTER('Table',[ID] =EARLIER([ID]) && [Start Date] <EARLIER('Table'[Start Date])),[Start Date]) 
var _max  = maxx(FILTER('Table',[ID] =EARLIER([ID]) && [Start Date] =_max1),[End Date] ) 

return if(ISBLANK(_max), BLANK(), DATEDIFF(_max,[Start Date],DAY))

[amitchandak]

@michael_knight , a new column

new column =

var _max = maxx(filter(Table, [ID] =earlier([ID]) && [Start Date] < earlier([Start Date]) ) , [End Date])

return

if( Isblank(_max), blank(), datediff(_max, [Start Date], day))

[michael_knight]

Hi @amitchandak 

Thank you for the reply. That's brilliant but one thing which I forgot to mention, if there is two processes on going, it must displace the earlier version. For example, for ID #3 

ID	Start Date	End Date	Expected Outcome
1	05/05/2021	23/05/2021	0
1	15/06/2021		23
2	25/03/2021		0
3	02/07/2021	06/07/2021	0
3	17/07/2021	20/07/2021	11
3	29/07/2021		9
3	05/08/2021
4	09/09/2021		0
5	25/09/2021	27/09/2021	0
5	15/10/2021	20/10/2021	18

There are two ongoing processes, it should only display the date difference for earliest version with the latest version being left blank

[amitchandak · Accepted solution]

@michael_knight , Try a new column like

Column = var _max1 = maxx(FILTER('Table',[ID] =EARLIER([ID]) && [Start Date] <EARLIER('Table'[Start Date])),[Start Date]) 
var _max  = maxx(FILTER('Table',[ID] =EARLIER([ID]) && [Start Date] =_max1),[End Date] ) 

return if(ISBLANK(_max), BLANK(), DATEDIFF(_max,[Start Date],DAY))

[michael_knight]

Brilliant, thank you @amitchandak