Rolling Sum within a group

[Anonymous]

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/Region

day_dt

New Cases

X 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/Region	day_dt	New Cases	X Day Rolling Sum
US	1/22/2020	0	0
US	1/23/2020	0	0
US	1/24/2020	1	0
US	1/25/2020	0	0
US	1/26/2020	3	0
US	1/27/2020	0	0
US	1/28/2020	0	0
US	1/29/2020	0	0
US	1/30/2020	0	0
US	1/31/2020	2	0
US	2/1/2020	1	0
US	2/2/2020	0	0
US	2/13/2020	5	0
US	2/14/2020	0	0
US	2/15/2020	0	0
US	2/16/2020	0	0
US	2/17/2020	0	0
US	2/18/2020	0	0
US	2/19/2020	0	0
US	2/20/2020	0	12
US	2/3/2020	-2	10
US	2/4/2020	0	10
US	2/5/2020	0	10
US	2/6/2020	0	9
US	2/7/2020	0	9
US	2/8/2020	0	6
US	2/9/2020	0	6
US	2/10/2020	0	6
US	2/11/2020	1	7
US	2/12/2020	0	7
US	2/21/2020	3	8
US	2/22/2020	0	7
US	2/23/2020	0	7
US	2/24/2020	36	38
US	2/25/2020	0	38
US	2/26/2020	6	44
US	2/27/2020	1	45
US	2/28/2020	2	47
US	2/29/2020	8	55
US	3/1/2020	6	61
US	3/2/2020	24	85
US	3/3/2020	20	107
US	3/4/2020	31	138
US	3/5/2020	68	206
US	3/6/2020	45	251
US	3/7/2020	140	391
US	3/8/2020	116	507
US	3/9/2020	65	572
US	3/10/2020	376	948
US	3/11/2020	322	1269
US	3/12/2020	382	1651
US	3/13/2020	516	2164
US	3/14/2020	548	2712
US	3/15/2020	772	3484
US	3/16/2020	1133	4581
US	3/17/2020	1789	6370
US	3/18/2020	1362	7726
US	3/19/2020	5894	13619
US	3/20/2020	5423	19040
US	3/21/2020	6389	25421
US	3/22/2020	7787	33202
US	3/23/2020	10571	43749
US	3/24/2020	9893	53622
US	3/25/2020	12038	65629
US	3/26/2020	18058	83619
US	3/27/2020	17821	101395
Argentina	2/27/2020	0	0
Argentina	2/28/2020	0	0
Argentina	2/29/2020	0	0
Argentina	3/1/2020	0	0
Argentina	3/2/2020	0	0
Argentina	3/3/2020	1	1
Argentina	3/4/2020	0	1
Argentina	3/5/2020	0	1
Argentina	3/6/2020	1	2
Argentina	3/7/2020	6	8
Argentina	3/8/2020	4	12
Argentina	3/9/2020	0	12
Argentina	3/10/2020	5	17
Argentina	3/11/2020	2	19
Argentina	3/12/2020	0	19
Argentina	3/13/2020	12	31
Argentina	3/14/2020	3	34
Argentina	3/15/2020	11	45
Argentina	3/16/2020	11	56
Argentina	3/17/2020	12	68
Argentina	3/18/2020	11	79
Argentina	3/19/2020	18	97
Argentina	3/20/2020	31	128
Argentina	3/21/2020	30	158
Argentina	3/22/2020	108	266
Argentina	3/23/2020	35	301
Argentina	3/24/2020	86	386
Argentina	3/25/2020	0	386
Argentina	3/26/2020	115	501
Argentina	3/27/2020	87	587
Argentina	3/28/2020	101	682

[Greg_Deckler · Accepted solution]

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]

@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]

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])

[Anonymous]

Thanks again!!

[Anonymous]

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]

Thank you so much!!