Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 (...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    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() )

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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() )