Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
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 |
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.
Hi,
I found a solution, thank you for your willingness to help.
You are welcome.