Window Function Trouble-shooting

Anonymous · ‎03-31-2023

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 two rows. [index] is a row count and [NAME] is the business unit. Here's my code:

window sum test = sumx(

window(

-2,rel,

0,rel,

,

orderby('base measures rolling'[index],asc),

,

PARTITIONBY('base measures rolling'[NAME])

),

[GM Projection])

Any suggestions on where to go from here?

Anonymous · ‎04-02-2023

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

Anonymous · ‎04-03-2023

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

Anonymous · ‎04-03-2023

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