Forum Discussion
Rolling 12 month standard deviation
Hi, I want to calculate the standard deviation of rolling 12 month sales amount by product. Can you help to fix the below measure in order to get the expected output as shown in table below. For example, Product A in 2022-07, the formula used is STDEV.S(sales from 2021-09 to 2022-07).
Std Dev = SUMX(
SUMMARIZE('Table','Calendar'[Month],'Table'[Product]),
IFERROR(CALCULATE(STDEVX.S('Table',[Monthly Sales]),
DATESINPERIOD( 'Calendar'[Date], MAX( 'Calendar'[Date] ), -12, MONTH )),0))
Here is the sample data.
| Month | Product | Monthly Sales | Expected Std Dev |
| 2021-09 | A | 18,118 | - |
| 2021-10 | A | 6,507 | 8,210 |
| 2021-11 | A | 14,113 | 5,898 |
| 2021-12 | A | 15,333 | 4,965 |
| 2022-01 | A | 12,614 | 4,319 |
| 2022-02 | A | 36,247 | 10,119 |
| 2022-03 | A | 36,842 | 11,862 |
| 2022-04 | A | 23,263 | 11,043 |
| 2022-05 | A | 16,862 | 10,397 |
| 2022-06 | A | 27,887 | 10,115 |
| 2022-07 | A | 34,177 | 10,412 |
| 2022-08 | A | 34,411 | 10,554 |
| 2021-09 | B | 12,053 | - |
| 2021-10 | B | 17,227 | 3,659 |
| 2021-11 | B | 15,164 | 2,605 |
| 2021-12 | B | 6,063 | 4,865 |
| 2022-01 | B | 8,317 | 4,633 |
| 2022-02 | B | 6,375 | 4,692 |
| 2022-03 | B | 16,112 | 4,720 |
| 2022-04 | B | 9,303 | 4,446 |
| 2022-05 | B | 21,992 | 5,471 |
| 2022-06 | B | 11,411 | 5,170 |
| 2022-07 | B | 13,474 | 4,915 |
| 2022-08 | B | 17,018 | 4,865 |
| 2021-09 | C | 13,703 | - |
| 2021-10 | C | 20,879 | 5,074 |
| 2021-11 | C | 10,327 | 5,389 |
| 2021-12 | C | 12,428 | 4,580 |
| 2022-01 | C | 8,810 | 4,673 |
| 2022-02 | C | 10,262 | 4,351 |
| 2022-03 | C | 18,347 | 4,503 |
| 2022-04 | C | 2,711 | 5,660 |
| 2022-05 | C | 7,804 | 5,492 |
| 2022-06 | C | 18,887 | 5,655 |
| 2022-07 | C | 5,048 | 5,806 |
| 2022-08 | C | 14,962 | 5,613 |
8 Replies
- Ashish_Mathur
Super User
Thanks Ashish_Mathur ! It works.
- Ashish_Mathur
You are welcome.
- Ashish_Mathur
Hi,
I do not have that file now. Share some data, explain the question and show the expected result. Share data in a format that can be pasted in an MS Excel file.