Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 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? 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for the reply. I'm still having the same issue. Here's some sample data with my result:

       

      cal monthyrNAMEGM Projectionkeyindexgm window test
      1202135$1,4903120210185$4,469
      2202135$62,6773120210286$188,031
      3202135$51,9223120210387$155,767
      4202135$63,7123120210488$191,135
      5202135$107,5483120210589$322,643
      6202135$114,3753120210690$343,124
      7202135$87,8923120210791$263,675
      8202135$190,1703120210892$570,510
      9202135$134,0573120210993$402,171
      10202135$146,0553120211094$438,164
      11202135$166,0703120211195$498,209
      12202135$77,8013120211296$233,404
      1202235$161,8873120220197$485,660
      2202235$403,5963120220298$1,210,788
      3202235$277,8583120220399$833,573
      4202235$225,52131202204100$676,562
      5202235$277,54331202205101$832,628
      6202235$205,67431202206102$617,023
      7202235$121,32231202207103$363,967
      8202235$247,12731202208104$741,380
      9202235$79,48731202209105$238,462
      10202235$195,40231202210106$586,206
      11202235$136,74331202211107$410,228
      12202235$118,63231202212108$355,897
      1202335$8,51531202301109$25,545
      2202335$156,76831202302110$470,304
      3202335$87,91231202303111$263,736
      4202335$121,71131202304112$365,134
      5202335$132,56031202305113$397,679
      6202335$120,30531202306114$360,916
      7202335$142,83131202307115$428,492
      8202335$202,16331202308116$606,488
      9202335$148,92231202309117$446,766
      10202335$148,44231202310118$445,326
      11202335$123,51631202311119$370,549
      12202335$51,25631202312120$153,768
      1201949$225,56740201901121$225,567
      2201949$567,37640201902122$1,134,752
      3201949$514,29840201903123$1,542,895
      4201949$505,67840201904124$1,517,033
      5201949$582,62340201905125$1,747,870
      6201949$515,15640201906126$1,545,468
      7201949$292,77840201907127$878,335
      8201949$605,05640201908128$1,815,169
      9201949$366,68740201909129$1,100,060
      10201949$463,11440201910130$1,389,342
      11201949$498,11940201911131$1,494,358
      12201949$194,72640201912132$584,177
      1202049$193,17440202001133$579,522
      2202049$438,46740202002134$1,315,400
      3202049$304,66540202003135$913,995
      4202049$357,46340202004136$1,072,389
      5202049$415,51240202005137$1,246,535
      6202049$296,35640202006138$889,067
      7202049$149,94240202007139$449,826
      8202049$420,38140202008140$1,261,143
      9202049$247,04640202009141$741,138
      10202049$329,87740202010142$989,630
      11202049$347,01140202011143$1,041,032
      12202049$192,00040202012144$576,000
      1202149$313,87140202101145$941,613
      2202149$480,46140202102146$1,441,383
      3202149$250,43440202103147$751,301
      4202149$405,54640202104148$1,216,638
      5202149$528,03040202105149$1,584,091
      6202149$381,28340202106150$1,143,849
      7202149$180,89140202107151$542,673
      8202149$475,42440202108152$1,426,273
      9202149$296,73040202109153$890,191
      10202149$397,02540202110154$1,191,075
      11202149$438,98540202111155$1,316,955
      12202149$287,50740202112156$862,521
      1202249$587,28640202201157$1,761,857
      2202249$1,038,33640202202158$3,115,009
      3202249$1,175,17140202203159$3,525,513
      4202249$892,90540202204160$2,678,716
      5202249$1,212,29840202205161$3,636,893
      6202249$692,30140202206162$2,076,903
      7202249$537,05840202207163$1,611,173
      8202249$853,15340202208164$2,559,459
      9202249$527,33440202209165$1,582,002
      10202249$595,17240202210166$1,785,516
      11202249$599,09340202211167$1,797,278
      12202249$473,09040202212168$1,419,269
      1202349$482,28640202301169$1,446,857
      2202349$676,52840202302170$2,029,583
      3202349$509,58840202303171$1,528,764
      4202349$499,10040202304172$1,497,300
      5202349$576,25540202305173$1,728,765
      6202349$518,63640202306174$1,555,907
      7202349$454,21140202307175$1,362,632
      8202349$823,49540202308176$2,470,486
      9202349$560,94640202309177$1,682,838
      10202349$610,70140202310178$1,832,102
      11202349$590,12440202311179$1,770,373
      12202349$221,06040202312180$663,181
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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