Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below expression showing a moving average over 12 months of sales, if i only want to show data where it has a full 12 months to calculate the average how can i do this?
rangeavg(above(Sum({<[Reporting Date]={">=$(=AddMonths(Max([Reporting Date]),-24))"}>} [Sales Qty (All)]),0,12)) ,
May be this?
If(RowNo(TOTAL) >= 12,
RangeAvg(Above(Sum({<[Reporting Date] = {">=$(=AddMonths(Max([Reporting Date]),-24))"}>} [Sales Qty (All)]), 0, 12))
)
Hello,
Can you please send us data to test
Thanks
Essentially I want the below with the Moving average only calculating if it has 12 data points.
[Reporting Date] | [Sales Qty (All)] | Moving Average |
1 | 10 | |
2 | 12 | |
3 | 13 | |
4 | 14 | |
5 | 11 | |
6 | 10 | |
7 | 12 | |
8 | 13 | |
9 | 14 | |
10 | 11 | |
11 | 10 | |
12 | 12 | 11.83333333 |
13 | 13 | 12.08333333 |
14 | 11 | 12 |
15 | 13 | 12 |
16 | 14 | 12 |
17 | 15 | 12.33333333 |
18 | 12 | 12.5 |
19 | 11 | 12.41666667 |
20 | 13 | 12.41666667 |
21 | 14 | 12.41666667 |
22 | 15 | 12.75 |
23 | 12 | 12.91666667 |
24 | 11 | 12.83333333 |
Thanks,Can you please give us an example of the result to have 🙂
Any luck on this, I was thinking something along the lines of If statement where a count of the "above" results is equal to or more than 12, is that possible?
May be this?
If(RowNo(TOTAL) >= 12,
RangeAvg(Above(Sum({<[Reporting Date] = {">=$(=AddMonths(Max([Reporting Date]),-24))"}>} [Sales Qty (All)]), 0, 12))
)
Perfect!