Forum Discussion
Window Function Trouble-shooting
I just discoved the WINDOW function and am trying to build a rolling sum in a calculated column. However, the result multiplies the current row by 3 rather than summing the current row and the prior ...
Thanks for the reply. I'm still having the same issue. Here's some sample data with my result:
| cal month | yr | NAME | GM Projection | key | index | gm window test |
| 1 | 2021 | 35 | $1,490 | 31202101 | 85 | $4,469 |
| 2 | 2021 | 35 | $62,677 | 31202102 | 86 | $188,031 |
| 3 | 2021 | 35 | $51,922 | 31202103 | 87 | $155,767 |
| 4 | 2021 | 35 | $63,712 | 31202104 | 88 | $191,135 |
| 5 | 2021 | 35 | $107,548 | 31202105 | 89 | $322,643 |
| 6 | 2021 | 35 | $114,375 | 31202106 | 90 | $343,124 |
| 7 | 2021 | 35 | $87,892 | 31202107 | 91 | $263,675 |
| 8 | 2021 | 35 | $190,170 | 31202108 | 92 | $570,510 |
| 9 | 2021 | 35 | $134,057 | 31202109 | 93 | $402,171 |
| 10 | 2021 | 35 | $146,055 | 31202110 | 94 | $438,164 |
| 11 | 2021 | 35 | $166,070 | 31202111 | 95 | $498,209 |
| 12 | 2021 | 35 | $77,801 | 31202112 | 96 | $233,404 |
| 1 | 2022 | 35 | $161,887 | 31202201 | 97 | $485,660 |
| 2 | 2022 | 35 | $403,596 | 31202202 | 98 | $1,210,788 |
| 3 | 2022 | 35 | $277,858 | 31202203 | 99 | $833,573 |
| 4 | 2022 | 35 | $225,521 | 31202204 | 100 | $676,562 |
| 5 | 2022 | 35 | $277,543 | 31202205 | 101 | $832,628 |
| 6 | 2022 | 35 | $205,674 | 31202206 | 102 | $617,023 |
| 7 | 2022 | 35 | $121,322 | 31202207 | 103 | $363,967 |
| 8 | 2022 | 35 | $247,127 | 31202208 | 104 | $741,380 |
| 9 | 2022 | 35 | $79,487 | 31202209 | 105 | $238,462 |
| 10 | 2022 | 35 | $195,402 | 31202210 | 106 | $586,206 |
| 11 | 2022 | 35 | $136,743 | 31202211 | 107 | $410,228 |
| 12 | 2022 | 35 | $118,632 | 31202212 | 108 | $355,897 |
| 1 | 2023 | 35 | $8,515 | 31202301 | 109 | $25,545 |
| 2 | 2023 | 35 | $156,768 | 31202302 | 110 | $470,304 |
| 3 | 2023 | 35 | $87,912 | 31202303 | 111 | $263,736 |
| 4 | 2023 | 35 | $121,711 | 31202304 | 112 | $365,134 |
| 5 | 2023 | 35 | $132,560 | 31202305 | 113 | $397,679 |
| 6 | 2023 | 35 | $120,305 | 31202306 | 114 | $360,916 |
| 7 | 2023 | 35 | $142,831 | 31202307 | 115 | $428,492 |
| 8 | 2023 | 35 | $202,163 | 31202308 | 116 | $606,488 |
| 9 | 2023 | 35 | $148,922 | 31202309 | 117 | $446,766 |
| 10 | 2023 | 35 | $148,442 | 31202310 | 118 | $445,326 |
| 11 | 2023 | 35 | $123,516 | 31202311 | 119 | $370,549 |
| 12 | 2023 | 35 | $51,256 | 31202312 | 120 | $153,768 |
| 1 | 2019 | 49 | $225,567 | 40201901 | 121 | $225,567 |
| 2 | 2019 | 49 | $567,376 | 40201902 | 122 | $1,134,752 |
| 3 | 2019 | 49 | $514,298 | 40201903 | 123 | $1,542,895 |
| 4 | 2019 | 49 | $505,678 | 40201904 | 124 | $1,517,033 |
| 5 | 2019 | 49 | $582,623 | 40201905 | 125 | $1,747,870 |
| 6 | 2019 | 49 | $515,156 | 40201906 | 126 | $1,545,468 |
| 7 | 2019 | 49 | $292,778 | 40201907 | 127 | $878,335 |
| 8 | 2019 | 49 | $605,056 | 40201908 | 128 | $1,815,169 |
| 9 | 2019 | 49 | $366,687 | 40201909 | 129 | $1,100,060 |
| 10 | 2019 | 49 | $463,114 | 40201910 | 130 | $1,389,342 |
| 11 | 2019 | 49 | $498,119 | 40201911 | 131 | $1,494,358 |
| 12 | 2019 | 49 | $194,726 | 40201912 | 132 | $584,177 |
| 1 | 2020 | 49 | $193,174 | 40202001 | 133 | $579,522 |
| 2 | 2020 | 49 | $438,467 | 40202002 | 134 | $1,315,400 |
| 3 | 2020 | 49 | $304,665 | 40202003 | 135 | $913,995 |
| 4 | 2020 | 49 | $357,463 | 40202004 | 136 | $1,072,389 |
| 5 | 2020 | 49 | $415,512 | 40202005 | 137 | $1,246,535 |
| 6 | 2020 | 49 | $296,356 | 40202006 | 138 | $889,067 |
| 7 | 2020 | 49 | $149,942 | 40202007 | 139 | $449,826 |
| 8 | 2020 | 49 | $420,381 | 40202008 | 140 | $1,261,143 |
| 9 | 2020 | 49 | $247,046 | 40202009 | 141 | $741,138 |
| 10 | 2020 | 49 | $329,877 | 40202010 | 142 | $989,630 |
| 11 | 2020 | 49 | $347,011 | 40202011 | 143 | $1,041,032 |
| 12 | 2020 | 49 | $192,000 | 40202012 | 144 | $576,000 |
| 1 | 2021 | 49 | $313,871 | 40202101 | 145 | $941,613 |
| 2 | 2021 | 49 | $480,461 | 40202102 | 146 | $1,441,383 |
| 3 | 2021 | 49 | $250,434 | 40202103 | 147 | $751,301 |
| 4 | 2021 | 49 | $405,546 | 40202104 | 148 | $1,216,638 |
| 5 | 2021 | 49 | $528,030 | 40202105 | 149 | $1,584,091 |
| 6 | 2021 | 49 | $381,283 | 40202106 | 150 | $1,143,849 |
| 7 | 2021 | 49 | $180,891 | 40202107 | 151 | $542,673 |
| 8 | 2021 | 49 | $475,424 | 40202108 | 152 | $1,426,273 |
| 9 | 2021 | 49 | $296,730 | 40202109 | 153 | $890,191 |
| 10 | 2021 | 49 | $397,025 | 40202110 | 154 | $1,191,075 |
| 11 | 2021 | 49 | $438,985 | 40202111 | 155 | $1,316,955 |
| 12 | 2021 | 49 | $287,507 | 40202112 | 156 | $862,521 |
| 1 | 2022 | 49 | $587,286 | 40202201 | 157 | $1,761,857 |
| 2 | 2022 | 49 | $1,038,336 | 40202202 | 158 | $3,115,009 |
| 3 | 2022 | 49 | $1,175,171 | 40202203 | 159 | $3,525,513 |
| 4 | 2022 | 49 | $892,905 | 40202204 | 160 | $2,678,716 |
| 5 | 2022 | 49 | $1,212,298 | 40202205 | 161 | $3,636,893 |
| 6 | 2022 | 49 | $692,301 | 40202206 | 162 | $2,076,903 |
| 7 | 2022 | 49 | $537,058 | 40202207 | 163 | $1,611,173 |
| 8 | 2022 | 49 | $853,153 | 40202208 | 164 | $2,559,459 |
| 9 | 2022 | 49 | $527,334 | 40202209 | 165 | $1,582,002 |
| 10 | 2022 | 49 | $595,172 | 40202210 | 166 | $1,785,516 |
| 11 | 2022 | 49 | $599,093 | 40202211 | 167 | $1,797,278 |
| 12 | 2022 | 49 | $473,090 | 40202212 | 168 | $1,419,269 |
| 1 | 2023 | 49 | $482,286 | 40202301 | 169 | $1,446,857 |
| 2 | 2023 | 49 | $676,528 | 40202302 | 170 | $2,029,583 |
| 3 | 2023 | 49 | $509,588 | 40202303 | 171 | $1,528,764 |
| 4 | 2023 | 49 | $499,100 | 40202304 | 172 | $1,497,300 |
| 5 | 2023 | 49 | $576,255 | 40202305 | 173 | $1,728,765 |
| 6 | 2023 | 49 | $518,636 | 40202306 | 174 | $1,555,907 |
| 7 | 2023 | 49 | $454,211 | 40202307 | 175 | $1,362,632 |
| 8 | 2023 | 49 | $823,495 | 40202308 | 176 | $2,470,486 |
| 9 | 2023 | 49 | $560,946 | 40202309 | 177 | $1,682,838 |
| 10 | 2023 | 49 | $610,701 | 40202310 | 178 | $1,832,102 |
| 11 | 2023 | 49 | $590,124 | 40202311 | 179 | $1,770,373 |
| 12 | 2023 | 49 | $221,060 | 40202312 | 180 | $663,181 |
Hi Anonymous ,
If you want a calculated column, try.
window sum test 1 =
CALCULATE(
SUM('Table'[GM Projection]),
FILTER(
ALL('Table'),
'Table'[index] >= EARLIER('Table'[index]) - 2
&& 'Table'[index] <= EARLIER('Table'[index])
)
)window sum test 2 =
CALCULATE(
SUM('Table'[GM Projection]),
WINDOW(
-2,REL,
0,REL,
,
ORDERBY('Table'[index])
),
ALL('Table')
)
Measure.
window sum test 3 =
SUMX(
WINDOW(
-2,REL,
0,REL,
,
ORDERBY('Table'[index],ASC)
),
[GM Projection]
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum