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!
