Forum Discussion
Helper IIICalculate average over monthly sums
Problem solved by standarzing the KPI. Every month's KPI is calculated using the 3-month moving average. Now it doesn't matter anymore if I looking at a quarter or single month. The calculation is always the same
Helper IIINot exactly:
What I am trying to accomplish is that:
When I display months - the formula will do (sum of inventory specific month + sum of inventory previous month) / 2
When I display quarters - the formula will do (sum of inventory last month of quarter + sum of inventory last month of previous quarter) / 2
Or it can take the average of all the sums per month within each quarter
I tried to extend the dataset a bit to show what I mean:
| 1/31/2023 | 2/28/2023 | 3/31/2023 | 4/30/2023 | 5/31/2023 | 6/30/2023 | ||
| Sum | Family1 | 115 | 130 | 115 | 126.5 | 143 | 126.5 |
| Family2 | 100 | 105 | 105 | 110 | 115.5 | 115.5 | |
| Ave | Family1 | 122.5 | 122.5 | 120.75 | 134.75 | 134.75 | |
| Family2 | 102.5 | 105 | 107.5 | 112.75 | 115.5 |
| Q1 | Q2 | ||
| Sum | Family1 | 115 | 126.5 |
| Family2 | 105 | 115.5 | |
| Ave | Family1 | 120.75 | |
| Family2 | 110.25 |
| otal Corp | Business group | Product Family | Product | Date | Quarter | Value |
| Corp | Business1 | Family1 | Prod1 | 1/31/2023 | Q1 | 100.00 |
| Corp | Business1 | Family1 | Prod2 | 1/31/2023 | Q1 | 15.00 |
| Corp | Business1 | Family2 | ProdA | 1/31/2023 | Q1 | 45.00 |
| Corp | Business1 | Family2 | ProdB | 1/31/2023 | Q1 | 55.00 |
| Corp | Business2 | Family3 | Prod1 | 1/31/2023 | Q1 | 250.00 |
| Corp | Business2 | Family3 | Prod2 | 1/31/2023 | Q1 | 300.00 |
| Corp | Business2 | Family4 | ProdA | 1/31/2023 | Q1 | 375.00 |
| Corp | Business2 | Family4 | ProdB | 1/31/2023 | Q1 | 400.00 |
| Corp | Business1 | Family1 | Prod1 | 2/28/2023 | Q1 | 105.00 |
| Corp | Business1 | Family1 | Prod2 | 2/28/2023 | Q1 | 25.00 |
| Corp | Business1 | Family2 | ProdA | 2/28/2023 | Q1 | 40.00 |
| Corp | Business1 | Family2 | ProdB | 2/28/2023 | Q1 | 65.00 |
| Corp | Business2 | Family3 | Prod1 | 2/28/2023 | Q1 | 260.00 |
| Corp | Business2 | Family3 | Prod2 | 2/28/2023 | Q1 | 315.00 |
| Corp | Business2 | Family4 | ProdA | 2/28/2023 | Q1 | 375.00 |
| Corp | Business2 | Family4 | ProdB | 2/28/2023 | Q1 | 400.00 |
| Corp | Business1 | Family1 | Prod1 | 3/31/2023 | Q1 | 95.00 |
| Corp | Business1 | Family1 | Prod2 | 3/31/2023 | Q1 | 20.00 |
| Corp | Business1 | Family2 | ProdA | 3/31/2023 | Q1 | 25.00 |
| Corp | Business1 | Family2 | ProdB | 3/31/2023 | Q1 | 80.00 |
| Corp | Business2 | Family3 | Prod1 | 3/31/2023 | Q1 | 270.00 |
| Corp | Business2 | Family3 | Prod2 | 3/31/2023 | Q1 | 330.00 |
| Corp | Business2 | Family4 | ProdA | 3/31/2023 | Q1 | 340.00 |
| Corp | Business2 | Family4 | ProdB | 3/31/2023 | Q1 | 380.00 |
| Corp | Business1 | Family1 | Prod1 | 4/30/2023 | Q2 | 110.00 |
| Corp | Business1 | Family1 | Prod2 | 4/30/2023 | Q2 | 16.50 |
| Corp | Business1 | Family2 | ProdA | 4/30/2023 | Q2 | 49.50 |
| Corp | Business1 | Family2 | ProdB | 4/30/2023 | Q2 | 60.50 |
| Corp | Business2 | Family3 | Prod1 | 4/30/2023 | Q2 | 275.00 |
| Corp | Business2 | Family3 | Prod2 | 4/30/2023 | Q2 | 330.00 |
| Corp | Business2 | Family4 | ProdA | 4/30/2023 | Q2 | 412.50 |
| Corp | Business2 | Family4 | ProdB | 4/30/2023 | Q2 | 440.00 |
| Corp | Business1 | Family1 | Prod1 | 5/31/2023 | Q2 | 115.50 |
| Corp | Business1 | Family1 | Prod2 | 5/31/2023 | Q2 | 27.50 |
| Corp | Business1 | Family2 | ProdA | 5/31/2023 | Q2 | 44.00 |
| Corp | Business1 | Family2 | ProdB | 5/31/2023 | Q2 | 71.50 |
| Corp | Business2 | Family3 | Prod1 | 5/31/2023 | Q2 | 286.00 |
| Corp | Business2 | Family3 | Prod2 | 5/31/2023 | Q2 | 346.50 |
| Corp | Business2 | Family4 | ProdA | 5/31/2023 | Q2 | 412.50 |
| Corp | Business2 | Family4 | ProdB | 5/31/2023 | Q2 | 440.00 |
| Corp | Business1 | Family1 | Prod1 | 6/30/2023 | Q2 | 104.50 |
| Corp | Business1 | Family1 | Prod2 | 6/30/2023 | Q2 | 22.00 |
| Corp | Business1 | Family2 | ProdA | 6/30/2023 | Q2 | 27.50 |
| Corp | Business1 | Family2 | ProdB | 6/30/2023 | Q2 | 88.00 |
| Corp | Business2 | Family3 | Prod1 | 6/30/2023 | Q2 | 297.00 |
| Corp | Business2 | Family3 | Prod2 | 6/30/2023 | Q2 | 363.00 |
| Corp | Business2 | Family4 | ProdA | 6/30/2023 | Q2 | 374.00 |
| Corp | Business2 | Family4 | ProdB | 6/30/2023 | Q2 | 418.00 |
Helper IIIProblem solved by standarzing the KPI. Every month's KPI is calculated using the 3-month moving average. Now it doesn't matter anymore if I looking at a quarter or single month. The calculation is always the same