I have a list of purchase prices per month for a large number of articles, I would like to calculate the running 12 months average per article.
I want to exclude zeros, for example if I purchased an article in January for 100 usd, nothing in february, the average price for february should still be 100, until a new purchase is made.
Also for the first 11 months, it should be the running total from the start, i.e January 2021's running total would only be for January 2021, February should be running total January-> February, March would be January-March. etc
Below is a mockup (The previous value (if larger than 0) "lives" and influences the average for 12 months if there is another purchase made, otherwise it lives "forever".
Super happy for all help with this I can get!
I want to exclude zeros, for example if I purchased an article in January for 100 usd, nothing in february, the average price for february should still be 100, until a new purchase is made.
Also for the first 11 months, it should be the running total from the start, i.e January 2021's running total would only be for January 2021, February should be running total January-> February, March would be January-March. etc
Below is a mockup (The previous value (if larger than 0) "lives" and influences the average for 12 months if there is another purchase made, otherwise it lives "forever".
Super happy for all help with this I can get!
| Product | Month | Price | Average L12 |
| A | Jan-21 | 0 | 0 |
| A | Feb-21 | 0 | 0 |
| A | Mar-21 | 0 | 0 |
| A | Apr-21 | 0 | 0 |
| A | May-21 | 100 | 100 |
| A | Jun-21 | 0 | 100 |
| A | Jul-21 | 0 | 100 |
| A | Aug-21 | 0 | 100 |
| A | Sep-21 | 0 | 100 |
| A | Oct-21 | 0 | 100 |
| A | Nov-21 | 200 | 150 |
| A | Dec-21 | 0 | 150 |
| A | Jan-22 | 0 | 150 |
| A | Feb-22 | 0 | 150 |
| A | Mar-22 | 0 | 150 |
| A | Apr-22 | 0 | 150 |
| A | May-22 | 0 | 200 |
| A | Jun-22 | 0 | 200 |
| A | Jul-22 | 0 | 200 |
| A | Aug-22 | 0 | 200 |
| A | Sep-22 | 300 | 250 |
| A | Oct-22 | 0 | 300 |
| A | Nov-22 | 0 | 300 |
| A | Dec-22 | 0 | 300 |
| A | Jan-23 | 0 | 300 |
| A | Feb-23 | 0 | 300 |
| A | Mar-23 | 0 | 300 |
| A | Apr-23 | 0 | 300 |
| A | May-23 | 0 | 300 |
| A | Jun-23 | 0 | 300 |
| A | Jul-23 | 0 | 300 |
| B | Jan-21 | 0 | 0 |
| B | Feb-21 | 0 | 0 |
| B | Mar-21 | 0 | 0 |
| B | Apr-21 | 0 | 0 |
| B | May-21 | 100 | 100 |
| B | Jun-21 | 0 | 100 |
| B | Jul-21 | 0 | 100 |
| B | Aug-21 | 0 | 100 |
| B | Sep-21 | 0 | 100 |
| B | Oct-21 | 0 | 100 |
| B | Nov-21 | 200 | 150 |
| B | Dec-21 | 0 | 150 |
| B | Jan-22 | 0 | 150 |
| B | Feb-22 | 0 | 150 |
| B | Mar-22 | 0 | 150 |
| B | Apr-22 | 0 | 150 |
| B | May-22 | 0 | 200 |
| B | Jun-22 | 0 | 200 |
| B | Jul-22 | 0 | 200 |
| B | Aug-22 | 0 | 200 |
| B | Sep-22 | 300 | 250 |
| B | Oct-22 | 0 | 300 |
| B | Nov-22 | 0 | 300 |
| B | Dec-22 | 0 | 300 |
| B | Jan-23 | 0 | 300 |
| B | Feb-23 | 0 | 300 |
| B | Mar-23 | 0 | 300 |
| B | Apr-23 | 0 | 300 |
| B | May-23 | 0 | 300 |
| B | Jun-23 | 0 | 300 |
| B | Jul-23 | 0 | 300 |
