Replace null values with conditional average

[Grella]

Help please!

I'm still fairly new to Power BI and am trying to replace null values in my data with the average of the previous 3 months with some conditions. 

I have simplified the 2 tables to demonstrate my scenario: 

- Units Sold table: shows the number of units sold by a particular employee in a particular month

- Employee Details table: shows if the employee is still employed with the company or has left (and the date their employment ended)

In my Units Sold table, i want to replace all the null values with the average from the previous 3 months data for that particular person, even if one month in the previous 3 is also a calculated average (see desired value for Jessica in 1/05/2021). Before calculating this average, i would need to be able to check the Employee Details table to ensure the person is still employed - any null values after the Emp. End date should remain null. 

Employee	Date	Units Sold	Expected Value
Jessica	1/01/2021	2875
Jessica	1/02/2021	3452
Jessica	1/03/2021	1217
Jessica	1/04/2021	null	2515
Jessica	1/05/2021	null	2395
Jessica	1/06/2021	5816
Jessica	1/07/2021	1056
Jessica	1/08/2021	null	3089
Jessica	1/09/2021	4956
Jessica	1/10/2021	2534
Jessica	1/11/2021	7512
Jessica	1/12/2021	3185
Abigail	1/01/2021	5672
Abigail	1/02/2021	8495
Abigail	1/03/2021	2356
Abigail	1/04/2021	null	5508
Abigail	1/05/2021	4675
Abigail	1/06/2021	1523
Abigail	1/07/2021	null	null
Abigail	1/08/2021	null	null
Abigail	1/09/2021	null	null
Abigail	1/10/2021	null	null
Abigail	1/11/2021	null	null
Abigail	1/12/2021	null	null

Employee	Employment Status	Emp. End date
Jessica	Employed
Abigail	Resigned	29/06/2021

Any help on the best way to tackle this in power query would be greatly appreciated!

[wdx223_Daniel]

= #table(Table.ColumnNames(UnitsSold),List.Accumulate(Table.ToRows(UnitsSold),{{},{},""},(x,y)=>let a=EmployeeDetails{[Employee=y{0},Employment Status="Resigned"]}?[#"Emp. End date"]? ??DateTime.LocalNow(),b=List.Average(List.LastN(x{1},3)) in if y{1}>=a then {x{0}&{y},{},y{0}} else if y{0}<>x{2} then {x{0}&{y},{y{2}},y{0}} else if y{2}<> null then {x{0}&{y},x{1}&{y{2}},y{0}} else {x{0}&{List.FirstN(y,2)&{b}},x{1}&{b},y{0}}){0})

[Grella]

Thanks for your response @wdx223_Daniel . Would you mind sharing the code for your first 2 steps (UnitsSold & EmployeeDetails)? I have pulled these two tables into power query as 2 separate tables, so not sure how you are referencing EmployeeDetails within the same table

[wdx223_Daniel]

let
    UnitsSold = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    EmployeeDetails = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
    Custom1 = #table(
                     Table.ColumnNames(UnitsSold),
                     List.Accumulate(
                                     Table.ToRows(UnitsSold),
                                     {{},{},""},
                                     (x,y)=>let
                                               a=EmployeeDetails{[Employee=y{0},Employment Status="Resigned"]}?[#"Emp. End date"]? ??DateTime.LocalNow(),
                                               b=List.Average(List.LastN(x{1},3))
                                            in
                                               if y{1}>=a then {x{0}&{y},{},y{0}} else
                                               if y{0}<>x{2} then {x{0}&{y},{y{2}},y{0}} else
                                               if y{2}<> null then {x{0}&{y},x{1}&{y{2}},y{0}}
                                               else {x{0}&{List.FirstN(y,2)&{b}},x{1}&{b},y{0}}
                                    ){0}
                    )
in
    Custom1

[Grella]

Would you still happen to have this file and can attach? I still can't seem to replicate the result, and i also don't fully understand the code in laymans terms to be able to troubleshoot exactly where i'm going wrong

[wdx223_Daniel]

wrote this code in a temp workbook without saving. sorry for that