Hi,
I have monthly sales data and want to calculate a 12-months moving average, see data and formula below. Everything works fine, apart from one month (Feb’2017) which is incorrect. The correct value should be 505.3846153, it is 529.8333333 instead.
Please notice that I only want to include Sales where ‘SalesDate’ is null or it matches the reporting date. My formula is therefore as follows:
RangeAvg(Above(Count(IF(IsNull(SalesDate)or(SalesDate=ReportingDate),'sold')),0,12))
The table below shows the result of the Count – this works fine when I do it in isolation, so I assume the problem is not there.
What am I doing wrong? Any help is appreciated.
Thanks,
Tom
ReportingMonth | Sales | Sales Moving Average |
2015-08 | 217 | 217 |
2015-09 | 214 | 215.5 |
2015-10 | 214 | 215.3333333 |
2015-11 | 214 | 215 |
2015-12 | 214 | 215 |
2016-01 | 212 | 214.6666667 |
2016-02 | 212 | 214.2857143 |
2016-03 | 202 | 213.25 |
2016-04 | 463 | 241.2222222 |
2016-05 | 531 | 270.2 |
2016-06 | 526 | 294.4545455 |
2016-07 | 542 | 315.1666667 |
2016-08 | 539 | 342 |
2016-09 | 591 | 373.5 |
2016-10 | 585 | 404.8333333 |
2016-11 | 584 | 436.0833333 |
2016-12 | 585 | 467.0833333 |
2017-01 | 579 | 497.5833333 |
2017-02 | 598 | 529.8333333 |
2017-03 | 409 | 550.75 |
2017-04 | 452 | 549.75 |
2017-05 | 453 | 543.5 |
2017-06 | 454 | 537.25 |
2017-07 | 455 | 529.9166667 |
2017-08 | 453 | 522.9166667 |
2017-09 | 459 | 512.0833333 |
2017-10 | 454 | 500.8333333 |
2017-11 | 453 | 489.6666667 |
2017-12 | 444 | 478.0833333 |
2018-01 | 446 | 467.0833333 |
2018-02 | 444 | 454.1666667 |
2018-03 | 418 | 452.4166667 |
2018-04 | 388 | 447.1666667 |
2018-05 | 392 | 441.8333333 |
2018-06 | 389 | 435.9166667 |
2018-07 | 390 | 430.5833333 |
2018-08 | 484 | 433.0833333 |
2018-09 | 466 | 434.1666667 |
2018-10 | 467 | 435.5833333 |
2018-11 | 467 | 436.8333333 |
2018-12 | 458 | 438 |
