Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling Sum within a group

I am working on a COVID-19 dashboard and have hit a wall. I have a situation where I am trying to produce a rolling sum for a number of days within a category, but there are some complexities. I could do this multiple ways in SQL, but I am new to PowerQuery and struggling. The examples I have seen aren't quite making sense to me so I was hoping someone could help.

 

Here is the Table: 

Country/Regionday_dtNew CasesX Day Rolling Sum

 

I would like to create a rolling sum value [X Day Rolling Sum] for [New Cases] for day_dt - X days within each [Country/Region].  (Lets say X = 21. We can hardcode it)

 

One catch is that the day_dt starts at 1/22/2020 for most countries, and later for others, depending on the first reported cases. That means once there are not enough rows for the rolling period, we would want to default to 0 if possible.

 

I am also open an excel formula! 

 

FYI - the purpose of this view is to project and active population X days out based on average recovery times for populations who do not report recovery data. I can also add a dim_date table if that would make this easier.

 

The table below is hardcoded with expected results. Thank you!

 

Country/Regionday_dtNew CasesX Day Rolling Sum
US1/22/202000
US1/23/202000
US1/24/202010
US1/25/202000
US1/26/202030
US1/27/202000
US1/28/202000
US1/29/202000
US1/30/202000
US1/31/202020
US2/1/202010
US2/2/202000
US2/13/202050
US2/14/202000
US2/15/202000
US2/16/202000
US2/17/202000
US2/18/202000
US2/19/202000
US2/20/2020012
US2/3/2020-210
US2/4/2020010
US2/5/2020010
US2/6/202009
US2/7/202009
US2/8/202006
US2/9/202006
US2/10/202006
US2/11/202017
US2/12/202007
US2/21/202038
US2/22/202007
US2/23/202007
US2/24/20203638
US2/25/2020038
US2/26/2020644
US2/27/2020145
US2/28/2020247
US2/29/2020855
US3/1/2020661
US3/2/20202485
US3/3/202020107
US3/4/202031138
US3/5/202068206
US3/6/202045251
US3/7/2020140391
US3/8/2020116507
US3/9/202065572
US3/10/2020376948
US3/11/20203221269
US3/12/20203821651
US3/13/20205162164
US3/14/20205482712
US3/15/20207723484
US3/16/202011334581
US3/17/202017896370
US3/18/202013627726
US3/19/2020589413619
US3/20/2020542319040
US3/21/2020638925421
US3/22/2020778733202
US3/23/20201057143749
US3/24/2020989353622
US3/25/20201203865629
US3/26/20201805883619
US3/27/202017821101395
Argentina2/27/202000
Argentina2/28/202000
Argentina2/29/202000
Argentina3/1/202000
Argentina3/2/202000
Argentina3/3/202011
Argentina3/4/202001
Argentina3/5/202001
Argentina3/6/202012
Argentina3/7/202068
Argentina3/8/2020412
Argentina3/9/2020012
Argentina3/10/2020517
Argentina3/11/2020219
Argentina3/12/2020019
Argentina3/13/20201231
Argentina3/14/2020334
Argentina3/15/20201145
Argentina3/16/20201156
Argentina3/17/20201268
Argentina3/18/20201179
Argentina3/19/20201897
Argentina3/20/202031128
Argentina3/21/202030158
Argentina3/22/2020108266
Argentina3/23/202035301
Argentina3/24/202086386
Argentina3/25/20200386
Argentina3/26/2020115501
Argentina3/27/202087587
Argentina3/28/2020101682
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    I believe it should be something like this:

     

    Column = 
    VAR __Table = 
        FILTER(
            'Table',
            [Country/Region] = EARLIER([Country/Region]) && 
                [day_dt] <= EARLIER([day_dt]) && [day_dt] >= ((EARLIER([day_dt]) - 21) * 1. + 1)
        )
    VAR __Count = COUNTROWS(__Table)
RETURN
    IF(__Count < 21,0,SUMX(__Table,[New Cases]))

    I also believe your numbers are wrong because your dates are out-of-order.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I believe it should be something like this:

     

    Column = 
    VAR __Table = 
        FILTER(
            'Table',
            [Country/Region] = EARLIER([Country/Region]) && 
                [day_dt] <= EARLIER([day_dt]) && [day_dt] >= ((EARLIER([day_dt]) - 21) * 1. + 1)
        )
    VAR __Count = COUNTROWS(__Table)
RETURN
    IF(__Count < 21,0,SUMX(__Table,[New Cases]))

    I also believe your numbers are wrong because your dates are out-of-order.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      One more question -- if I wanted to add the column first via EDIT QUERY -- CUSTOM COLUMN, what whould the code look like? 

       

      I ultimately would like to use this column in another calculation and it seems like it would be easier if it was persistent, no? 

       

      I am sorry for teh novice questions.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler One more request, I am sorry...

       

      How do I count the New Cases when there are less than 21 rows remaining? I could probably play outer join games with a dim_date table, but is there a way to sum everything that is there is the rows are < rolling day value?

       

      Thank you!

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Well, that should just be:

         

        Column = 
    VAR __Table = 
        FILTER(
            'Table',
            [Country/Region] = EARLIER([Country/Region]) && 
                [day_dt] <= EARLIER([day_dt]) && [day_dt] >= ((EARLIER([day_dt]) - 21) * 1. + 1)
        )
    VAR __Count = COUNTROWS(__Table)
RETURN
    SUMX(__Table,[New Cases])