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 ...
Hi Anonymous ,
Please try.
window sum test =
SUMX (
WINDOW (
-2,
REL,
0,
REL,
,
ORDERBY ( 'Base Measure Rolling'[index], ASC,
'Base Measure Rolling'[NAME], ASC )
),
[GM Projection]
)
If this doesn't work, can you provide some sample data without privacy for testing?
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
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 TeamIf 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