Forum Discussion

ripstaur's avatar
ripstaur
Icon for Helper III rankHelper III
6 years ago

5-Day Moving Average

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 differenc...
daxer-almighty's avatar
daxer-almighty
Icon for Solution Sage rankSolution Sage
6 years ago
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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    6 years ago
    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's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        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.