 # Moving average not average

Hi

Can you explain how this is working please?

I was trying to work out a moving 7 day average but the normal pattern seems to ignore days when the key measure = 0

eg. sales of 50 on 5 days should give a 7 day average of 250/7 ie 35.7 but it gives 50

If I change the formula to the following this seems to work

``````Avg Calls 1W MA =
AVERAGEX(
DATESINPERIOD( Dates[Date],
LASTDATE( Dates[Date] ),-7,DAY),
[# of Calls] **+ 0** )
``````

Why is this?

Thanks

Likely depends on the measures # of calls.

If you add in IF( ISBLANK( measure ), 0 ) in the initial formula here this should also sort it out I believe.

Would have to see the actual model to truly understand it exactly as it could be for various reasons.

Thanks
Sam

@AliB,
Since there are holes in your date where there no, looks like, calls. You’d be hard pressed to use the built-in time intelligence.

I think the following will work, or might need small tweaks:
Moving Average not Counting Zero=
VAR __MovingAvgLength= 7
VAR __CurrentDate= MAX( DATETABLE[DATECOLUMN])
RETURN

``````CALCULATE(
IF(
COUNT( DATETABLE[DATECOLUMN]) >= __MovingAvgLength, //Make sure there are enough dates to produce an average
DIVIDE(
[MEASURE],

/*Here you are using your sales table as a filter for date
so only get the dates for which there are sales */
CALCULATE(
COUNT (DATETABLE[DATECOLUMN]),
FactTable
)
),
FILTER(
ALL( DATETABLE[DATECOLUMN]),
DATETABLE[DATECOLUMN] >= ( __CurrentDate - __MovingAvgLength)
&&
DATETABLE[DATECOLUMN] <= __CurrentDate
)

)
)
`````` Hi, Thanks for the replies

I have a date table so I believe inbuilt time intelligence should work. the averages weren’t working correctly because it didn’t take any notice of days without a value but adding the +0 to the formula appears to work ie it replaces blank with 0

Is it a simpler / inbuilt shortcut for Sam’s IF(ISBLANK([measure],0) or some little quirk in the language that may get fixed therefore should I switch to the IF statement mentioned as a safer solution …

@AliB,
I misread your original request. Instead of ignoring blank days ( which is what my function would do you) you should use sam’s formula where it checks to see if the measure is blank and if that is true return a 0. It’s not a quirk of DAX, it’s just the way DAX handles blank values. Blanks <> 0, which is why you to have to explicitly define blanks= 0 