Forum Discussion
Rolling Sum within a group
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.
Community ChampionI 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.
Thank you so much!!
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.
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])Thanks again!!