PowerPivot - Moving Average

# PowerPivot - Moving Average

• Sunday, February 17, 2013 5:25 PM

I'm trying to create a moving average in my model. Looking for some assistance.

I tried following the details in Alberto Ferrari's blog here. But I could not get the DayNumber measure working, the syntax did not seem correct and I could not correct it.

My model has a Fact table containing a list of cases, joining to a Date table via Created Date. I have a second relationship (Inactive) to the Date table on the ClosedDate column. I have a measure: Case Closed Count:=CALCULATE(COUNTROWS('Case'), USERELATIONSHIP('Case'[ClosedDateKey], 'Date'[DateKey])).

I would like a measure getting the sum of Case Closed Count for the last three days of the current context. I then plan to divide that number by 3 to get the moving 3-day-average.

One other piece of logic I would like to consider - if the last day is TODAY, then the previous 3 days are used - the data is updated every 15 minutes, so doing this at 09:00 in the morning would skew the average, as it's not a fully completed day.

Any assistance is appreciated.

### All Replies

• Monday, February 18, 2013 11:51 AM

In his post, Alberto makes use of the EARLIER function which returns a value from an earlier row context. This only works in an iterating expression, when this expression is evaluated in an existing row context (an other iterating expression, or a calculated column.)

Does the following meet the requirement (not tested)?

[Case Closed Count - Last 3 days] :=
CALCULATE(
COUNTROWS('Case')
, USERELATIONSHIP('Case'[ClosedDateKey], 'Date'[DateKey])
, DATESBETWEEN(
, 'Date'[DateKey]
)
)

The last one can done with an IF expression using TODAY() function, and adapting the pattern above.

The Data Specialist (Blog)

• Monday, February 18, 2013 4:08 PM

Benzane -

Here's a link to an approach using just a calculated measure that Javier Guillen wrote a while back.

Hope that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Tuesday, February 19, 2013 8:27 AM

Thank you Laurent, yes this is about what I am looking for. Only one small amendment I needed, to add LASTNONBLANK:

[Case Closed Count - Last 3 days]:=CALCULATE(
COUNTROWS('Case')
, USERELATIONSHIP('Case'[ClosedDateKey], 'Date'[DateKey])
,   DATESBETWEEN( 'Date'[Date],