Forum Discussion
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...
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 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!
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])Thanks again!!