Forum Discussion
5-Day Moving Average
I did cobble this solution together. It's not elegant - it is my rookie brute-force solution. It works, though. I'm sure there is a function that could radically streamline this code. I copied this in from a .txt file, and can't seem to get the tabs working...
Five Day MA New Cases =
VAR CasesPreviousDay =
CALCULATE (
SUM ( 'Covid-confirmed'[New Cases] ),
FILTER (
ALL ( 'Covid-confirmed' ),
'Covid-confirmed'[Date]
= EARLIER ( 'Covid-confirmed'[Date] ) - 1
&& 'Covid-confirmed'[FIPS Code] = EARLIER ( 'Covid-confirmed'[FIPS Code] )
)
)
VAR CasesTwoDaysAgo =
CALCULATE (
SUM ( 'Covid-confirmed'[New Cases] ),
FILTER (
ALL ( 'Covid-confirmed' ),
'Covid-confirmed'[Date]
= EARLIER ( 'Covid-confirmed'[Date] ) - 2
&& 'Covid-confirmed'[FIPS Code] = EARLIER ( 'Covid-confirmed'[FIPS Code] )
)
)
VAR CasesThreeDaysAgo =
CALCULATE (
SUM ( 'Covid-confirmed'[New Cases] ),
FILTER (
ALL ( 'Covid-confirmed' ),
'Covid-confirmed'[Date]
= EARLIER ( 'Covid-confirmed'[Date] ) - 1
&& 'Covid-confirmed'[FIPS Code] = EARLIER ( 'Covid-confirmed'[FIPS Code] )
)
)
VAR CasesFourDaysAgo =
CALCULATE (
SUM ( 'Covid-confirmed'[New Cases] ),
FILTER (
ALL ( 'Covid-confirmed' ),
'Covid-confirmed'[Date]
= EARLIER ( 'Covid-confirmed'[Date] ) - 1
&& 'Covid-confirmed'[FIPS Code] = EARLIER ( 'Covid-confirmed'[FIPS Code] )
)
)
RETURN
('Covid-confirmed'[New Cases] + CasesPreviousDay + CasesTwoDaysAgo + CasesThreeDaysAgo + CasesFourDaysAgo) / 5