Want to create a single-value average from prior three months

[paschott]

We had a report that was calculating an average from the prior 3 months (not including the current month). I'm trying to recreate that in DAX now and not sure how to go about it.

I tried:

Prev3MonthsAvgVisits = CALCULATE(AVERAGE(MonthlySummary[Visits]), DATESBETWEEN(dimDate[Date], DATEADD(dimDate[Date], -4, MONTH), DATEADD(dimDate[Date], -1, MONTH)))

That's valid DAX and seems to be what I want, but when I try to use that in my table, I get "A table of multiple values was supplied where a single value was expected."

How would I go about getting this value to work and return a single value per row rather than the table short of some direct query? Is there a column I can create that would do something similar?  Am I going about this in completely the wrong manner?

[Phil_Seamark]

Hi @paschott

I think this might be close...

Measure = CALCULATE(
			AVERAGE('MonthlySummary'[Visits]) ,
			DATESINPERIOD(
				'MonthlySummary'[Month],
				DATEADD(
					LASTDATE('MonthlySummary'[Month]),
					-1,
					MONTH
					),
				-3,
			MONTH)
			)

[paschott · Accepted solution]

Turns out I was looking for an AVERAGEX function instead:

AVERAGEX(

    DATESBETWEEN(

    'dimDate'[Date],

    DATEADD(LASTDATE('dimDate'[Date]), -4, MONTH),

    DATEADD(LASTDATE('dimDate'[Date]), -1, MONTH)

  ),

  CALCULATE(SUM('MonthlySummary'[Visits]))

)

[v-yulgu-msft]

Hi @paschott,

---

AVERAGEX(

    DATESBETWEEN(

    'dimDate'[Date],

    DATEADD(LASTDATE('dimDate'[Date]), -4, MONTH),

    DATEADD(LASTDATE('dimDate'[Date]), -1, MONTH)

  ),

  CALCULATE(SUM('MonthlySummary'[Visits]))

)

---

Have you resolved original problem and achieved your requirement with above modified formula? If yes, would you please kindly mark your shared solution as an answer so that it can benefit more users? If not, can you post the image of the unexpected result you got?

Regards,
Yuliana Gu