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 coul...
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.

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