Help on Imputing the blank date column with the average of difference between the 2 previous dates

[Anonymous]

Hi Folks,

I need your urgent help where I have to create a new calculated column of date, where if the status is finish and the start date is blank then calculate the average of date difference (Start date & Finish date) of all the activities where the status is finish and impute the new start date considering the finish date.

For example: In the given below data set Id 4 has a blank start date so, I want to create a new calculated column where the dax calculates the avearge of days difference between start date and finish date where status is finish [(58+75+60+59)/4 = 65.5 ] so that start date of Id 4 would be 23/01/2020 (28/03/2020 -65 days)

Id	Status	Start date	Finish Date  Days Difference
1	Finish	01/01/2020	28/02/2020             58
2	On-Going		0
3	On-Going	15/01/2020
4	Finish		28/03/2020
5	Finish		15/04/2020
6	Finish	15/02/2020	30/04/2020             75
7	Finish	30/01/2020	30/03/2020             60
8	On Going	01/02/2020
9	Finish		30/04/2020
10	Finish	01/04/2020	30/05/2020             59

[Anonymous]

Hi @amitchandak ,
Thanks for your help.
This Dax code was a major relief.
Once again Thank You! 🙂

[amitchandak]

@Anonymous , Create a new column like

new column =
var _1 = AverageX(filter(Table, not(isblank([Start Date])) && not(isblank([Finish Date])) , datediff([Start Date], [Finish date], day))
return
Switch(True() ,not(isblank([Start Date])) , [Start Date],
isblank([Start Date]) && not(isblank([Finish Date])), [Finish Date] - _1,
blank())

[Anonymous]

Hi @amitchandak ,

Thank you for your help!
I just have a concern , that the new column should only calculate the new date when the status is "Finish" and not "On-Going".

[amitchandak · Accepted solution]

@Anonymous , add one more condition

new column =
var _1 = AverageX(filter(Table, not(isblank([Start Date])) && not(isblank([Finish Date])) , datediff([Start Date], [Finish date], day))
return

if([status]= "Finish,
Switch(True() ,not(isblank([Start Date])) , [Start Date],
isblank([Start Date]) && not(isblank([Finish Date])), [Finish Date] - _1,
blank()) , blank() )