Moving average not average

DAX DAX Calculations
1

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

2

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

3

@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
        )

    )
)

Enterprise%20DNA%20Expert%20-%20Small

4

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 …

5

@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

Enterprise%20DNA%20Expert%20-%20Small