5-Day Moving Average

[ripstaur]

I have a dataset from GitHub containing cumulative daily Covid19 Deaths and Confirmed cases. It took me a while, but I finally found a solution to one problem, which was calculating a daily difference so I could get "new cases" and "new deaths." This seemed to be impossible in M but I did modify a solution I found for DAX, and it worked. 

Now, however, I have spent two days trying to find a way to get a 5- or 7-day moving average. The expression would need to work for the county level, but I would also need to be able to aggregate at the state level and the "entire country" grand-total level. 

This is pretty easy to do in Tableau, and maybe it is as well in Power BI, but it's very difficult to find the actual answer I need. I either get cryptic error messages or wildly incorrect values when I try the various solutions I find in these online blog posts. DAX, M, Power BI...they all need some debugging function and error tracing. 

[ripstaur]

I want to thank everyone for their advice...I had tried adapting similar solutions, but could not get anything to work for what I needed. I recognize the problem of not being able to post the data...the data set is large.  I could pare it down to just a couple of counties and 10-20 days, I guess, and post that. If you look at the inelegant solution I ended up with, you might be able to tell from that what I would need to do to write a smarter DAX query. 

Best regards,

Rip

[ripstaur]

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

[Greg_Deckler]

@ripstaur - I like @Fowmy 's solution, seems like it should work. Nice and simple. There is a Quick Measure Gallery submission that should work as well.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Average/m-p/160720#M3

Could also be done without the time intelligence functions if necessary (in an RLS or Direct Query situation), in theory:

Moving 5 Day Average =
  VAR __MaxDay = MAX('Table'[Date])
RETURN
  AVERAGEX(FILTER(ALL('Table'),[Date]<=__MaxDay && [Date]>=__MaxDay-5),[Value])

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

But, ultimately, actual sample data would be tremendously helpful to be very specific with a solution.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[daxer-almighty]

And I don't like @Fowmy's solution as it breaks down when we are too close to the left edge of the calendar (Dates).

[amitchandak]

@daxer-almighty , How about taking a sum and divide by days in @Fowmy  solution.

As mesures 

Rolling 5 = Divide(CALCULATE(Sum('Table'[Daily Cases]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Day)) ,CALCULATE(distinctcount('Table'[Date]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Day)))

or

Rolling 5 = CALCULATE(Divide(Sum('Table'[Daily Cases]),distinctcount('Table'[Date])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Day))

With a date table.

[daxer-almighty]

@amitchandak

You can't calculate 5D average if you don't have 5 days (too close to the left edge of the calendar), so you're targeting a completely different area that has nothing to do with the problem I'm talking about.

[amitchandak]

@daxer-almighty , that is the same idea to count date from the table in the denominator. So if there are not fine days, it divides that many number of days 

[Anonymous]

@amitchandak

"So if there are not fine days, it divides that many number of days"

Such a calculation is NOT 5D average. You're distorting the data this way.

[daxer-almighty]

// Depending on the model you have...
// But let's assume that:
// You have a Dates table that's connected
// to your fact table. Say the fact table
// is Cases and stores the following columns:
// 1. Date (Dates joins on this field)
// 2. CityID
// 3. NewCaseCount
// 4. DeathCount
// There'll be other dimensions but they're
// not relevant. Of course, fact table should
// have all its columns hidden. Slicing only
// via dimensions is allowed.

[New Cases] = SUM( Cases[NewCaseCount] )

[New Case Avg (5D)] =
// change this to 7 to get a weekly
// moving avg
var __numOfDays = 5 
var __lastVisibleDay = MAX( Dates[Date] )
var __daysToAvgOver =
    DATESINPERIOD(
        Dates[Date],
        __lastVisibleDay,
        -__numOfDays,
        DAY
    )
var __enoughDays =
    COUNTROWS( __daysToAvgOver ) = __numOfDays
var __avg =
    if( __enoughDays,
        CALCULATE(
            [New Cases],
            // this will work OK if
            // Dates is marked as a
            // Date table in the model
            __daysToAvgOver
        )
    )
return
    __avg

[Deaths] = SUM( Cases[DeathCount] )

[Death Count Avg (5D)] =
// change this to 7 to get a weekly
// moving avg
var __numOfDays = 5 
var __lastVisibleDay = MAX( Dates[Date] )
var __daysToAvgOver =
    DATESINPERIOD(
        Dates[Date],
        __lastVisibleDay,
        -__numOfDays,
        DAY
    )
var __enoughDays =
    COUNTROWS( __daysToAvgOver ) = __numOfDays
var __avg =
    if( __enoughDays,
        CALCULATE(
            [Deaths],
            // this will work OK if
            // Dates is marked as a
            // Date table in the model
            __daysToAvgOver
        )
    )
return
    __avg    
    
// By the way, in M you can do anything,
// even more than in DAX, so it's certain
// that the calculation you're talking about
// CAN BE done in M.

[ripstaur]

Thanks for this, and for the advice about M and DAX. I think I can adapt some of this to streamline my code. The one thing I don't see in this solution is a way to aggregate (or filter - not sure what the right word is in DAX/M) by FIPS Code, so I'm essentially getting the averages by county. I'm also not concerned over the number of days available...this record is long, and I don't show the first couple of weeks in most of my visualizations. 

I am not much of a coder - I took some basic (not VB, basic) classes years ago and used to be able to write pretty good stuff in that, and for several years I wrote databases in DB3 and DB4, but I got away from that and have been mostly doing analysis using SPSS and Minitab until a few years ago, when I was on a job that where they were using Qlikview, so I got certified as a Qlikview developer. Then they shifted to Tableau, and I was fat, dumb and happy using Tableau. Now I am working with an organization that decided to shift from Tableau to Power BI "to save money." So now I am in this environment, and having to code things that were simpler in other packages, using two new and different languages, without any formal training in either one.

Last week my challenge with this dataset was to find daily differences. I was unable to find anything in M that would do it (although I do believe there has to be that functionality, and would rather have done it there, in "Edit Queries"). I did find some DAX code in one of the Power BI or Power Query forums. I adapted it, and then ended up just modifying and iterating it to get the solution I wrote above. 

[Fowmy]

@ripstaur 

Create the following measure to calculate the 5 days moving average of daily cases. Make sure you have created a dates table and linked it to the case table. This should work when you filter by country or state.

5 Days MA = 
IF( NOT ISEMPTY('Table'),
    AVERAGEX(
        DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-5,DAY),
        CALCULATE(SUM('Table'[Daily Cases]))
    )
)

If this doesn't answer your question, share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn