create table #urgent(
     [name]     nvarchar(256)
    ,[rowid]    int
    ,[type]     nvarchar(256)
    ,[company]  nvarchar(256)
    ,[date]     datetime
    ,[kpi]      decimal(19, 2)
);

insert into #urgent values
 ('Activity Plan   ', 1, 'Low ', 'MS', '1/01/2018', 10  )
,('Activity Plan   ', 1, 'Low ', 'MS', '1/02/2018', 20  )
,('Activity Plan   ', 1, 'Low ', 'MS', '1/03/2018', 30  )
,('Activity Plan   ', 1, 'Low ', 'MS', '1/04/2018', NULL)
,('Activity Plan   ', 1, 'Low ', 'MS', '1/05/2018', NULL)
,('Activity Plan   ', 1, 'Low ', 'MS', '1/06/2018', NULL)
,('Activity Plan   ', 1, 'Low ', 'MS', '1/07/2018', NULL)
,('Appointment     ', 2, 'High', 'MS', '1/01/2018', 12  )
,('Appointment     ', 2, 'High', 'MS', '1/02/2018', 35  )
,('Appointment     ', 2, 'High', 'MS', '1/03/2018', 44  )
,('Appointment     ', 2, 'High', 'MS', '1/04/2018', NULL)
,('Appointment     ', 2, 'High', 'MS', '1/05/2018', NULL)
,('Appointment     ', 2, 'High', 'MS', '1/06/2018', NULL)
,('Appointment     ', 2, 'High', 'MS', '1/07/2018', NULL)


select
     [name]     =   [name]
    ,[rowid]    =   [rowid]
    ,[type]     =   [type]
    ,[company]  =   [company]
    ,[date]     =   [date]
    ,[kpi]      =   [kpi]
    ,[value]    =   iif([rowid] = 1, isnull([kpi], [lvalue].[value]), isnull([kpi], [avgvalue].[value]))
from
    #urgent as  [u]
outer apply
    (
        select top 1 [value] = [kpi] from #urgent where [rowid] = 1 and [kpi] is not null order by [date] desc
    ) as [lvalue]
outer apply
    (
        select [value] = avg([kpi]) from #urgent where [rowid] = 2  and [kpi] is not null
    ) as [avgvalue];

drop table #urgent;