topic Re: AverageX and Excel Disagree in DAX Commands and Tips

AverageX and Excel Disagree

Anonymous — Fri, 05 Jul 2024 18:28:09 GMT

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.

Re: AverageX and Excel Disagree

OwenAuger — Sat, 06 Jul 2024 07:53:58 GMT

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

Re: AverageX and Excel Disagree

Anonymous — Mon, 08 Jul 2024 14:02:45 GMT

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

Re: AverageX and Excel Disagree

Anonymous — Mon, 10 Feb 2025 19:02:37 GMT

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