AverageX and Excel Disagree

[Anonymous]

Interesting situation here.

Using averageX to claculate the last X months returns values that differ from the same calculations in Excel.

The dark blue line is the number being averaged.

The Light blue line is the resultant average.

The first result (last number in the visual...13.11), is dead on with the Excel result:

But the numbers diverge from that point on:

The number that is being averaged (M_ComplaintsPer1MEA) is the result of a series of measures.

Please Wax Poetic

Update: All columns and measures inolved are defined as decimal with 4 decimal places.

[OwenAuger · Accepted solution]

Hiya @Anonymous 

While the June 2024 12-month average looks correct, the earlier averages look to be 11 month averages. Not sure whether the issue is measure definition or something else in the model.

Here is how I would write the Avg of previous 12 measure myself. This assumes you have a 'Date' table set up in the usual way with a "Year Month" or equivalent column:

Avg of previous 12 = 
CALCULATE (
    AVERAGEX (
        VALUES ( 'Date'[Year Month] ),
        [M_CompIaintsPerIMEA]
    ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
)

Note: When fewer than 12 months are available, this measure will average the available months (can be modified to return blank instead).

Are you able to get something like this working?

PBIX attached.

Regards

[Anonymous]

I tried your Dax and still eneded up with the same numbers. But when I read where you mention it appareaed to be the average of he last 11 instead of 12, I decided to change the -12 in my Dax to -13 and shazam!

Either my spreadsheet was doing a 13 month or Bi counts funny.

M_CasesRolling12MosEA =

VAR ReferenceDate = MAX('calendar'[date])

VAR PreviousDates =

    DATESINPERIOD(

    'presentation_calandar'[Date],

    ReferenceDate,

    -13,

    MONTH

    )

VAR Result =

    CALCULATE(

    AVERAGEX(

        DATESINPERIOD(

            'calendar'[Month & Year],

            LASTDATE('calendar'[Month & Year]),

            -13,

            MONTH

        ),

        [M_ComplaintsPer1MEAMonth]

    ),

        ALL('calendar'),

        KEEPFILTERS(PreviousDates),

        USERELATIONSHIP('calendar'[date],'presentation_calandar'[Date])

        )

RETURN

Result

[akirpach]

This worked for me as well! Thanks for sharing this.