Rolling 12 month standard deviation

[PBI_newuser]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[alts]

Hello,

I have a similar problem, and I think your solution would solve it. When I log in to view the PBI file, it doesn't seem to be available, could you please re-upload the file or show somehow, how you achieved that solution?

I look forward to your prompt reply, thanks in advance.

[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.

[alts]

Essentially, I need to figure out the deviation and moving average of each person's income from the last 12 months. We use the available months for the months that are not yet 12 months old, provided that they are the last 12 months exclusive of the "current" month. For instance, the table indicates that the quantities from 2024-01 to 2023-02 = 241,059.21 for product B represent the standard deviation for 2024-02. In addition, if I remove the periods in the table, I need to be able to see both the deviation and the average of the last 12 months, for each person, according to the desired month.

Individual	Month	Income	Expected Standard Deviation	Expected Average
A	2023-01	-	#DIV/0!	-
A	2023-02	-	#DIV/0!	-
A	2023-03	136.00	-	-
A	2023-04	-	78.52	45.33
A	2023-05	824,566.32	68.00	34.00
A	2023-06	453,328.17	368,742.07	164,940.46
A	2023-07	355,389.26	350,196.82	213,005.08
A	2023-08	333,465.67	324,182.60	233,345.68
A	2023-09	529,647.74	302,214.96	245,860.68
A	2023-10	731,016.29	298,103.15	277,392.57
A	2023-11	862,649.95	315,545.55	322,754.95
A	2023-12	271,160.81	340,750.46	371,836.31
A	2024-01	617,594.61	326,190.09	363,446.68
A	2024-02	332,079.79	320,159.12	382,996.52
A	2024-03	-	285,502.90	442,586.22
A	2024-04	-	285,522.06	442,574.88
…	…	…	…	…
B	2023-01	636,676.71	#DIV/0!	636,676.71
B	2023-02	1,011,451.91	#DIV/0!	636,676.71
B	2023-03	1,102,140.29	265,006.09	824,064.31
B	2023-04	711,572.84	246,758.04	916,756.30
B	2023-05	472,391.74	226,093.09	865,460.44
B	2023-06	323,475.64	263,133.35	786,846.70
B	2023-07	469,373.90	301,954.94	709,618.19
B	2023-08	513,901.38	290,217.14	675,297.58
B	2023-09	621,465.98	274,681.26	655,123.05
B	2023-10	851,559.10	257,185.61	651,383.38
B	2023-11	705,238.00	250,603.44	671,400.95
B	2023-12	527,564.30	237,962.11	674,477.04
B	2024-01	422,131.01	230,817.59	662,234.32
B	2024-02	508,957.57	241,059.21	644,355.51
B	2024-03	-	213,570.53	602,480.98
B	2024-04	-	216,129.99	510,635.96

[Ashish_Mathur]

Hi,

In an MS Excel file, enter your data in sheet1 and show the expected result in sheet2.  Sheets2 should be formula driven.  I will understand those formulas and convert them to their DAX equivalents.

[alts]

Hi,
I found a solution, thank you for your willingness to help.

[PBI_newuser]

Thanks @Ashish_Mathur ! It works. 

[Ashish_Mathur]

You are welcome.