Need Rolling Average But Quick Measure Not Working

[Anonymous]

Hi everyone,

My team is trying to calculate a rolling average of quarterly numbers which will be used to calculate projections for upcoming quarters.  I've tried using the Rolling Average quick measure, but it just seems to be pulling the original value.  I can't seem to figure out what I'm doing wrong, so I'm hoping for a little guidance.  

I'd like to be able to take the average delta of the previous 2 quarters and project next quarter's performance based on this average.  If we were +10 in Q1 and -20 in Q2, I would expect to show a projection for Q3 of -5  (10 - 20 = -10 / 2 = -5).  Is this the correct math?  If so, how do I represent this in Power BI?

Here's some sample data based on my assumed math from above:

Year	Quarter	Goal	Actual	Delta	Projection (-2 Q)
2020	1	39079	38942	-137
2020	2	39149	39049	-100
2020	3	39199	39040	-159	39080
2020	4	39399	39454	55	39402
2021	1	39524		3 (proj)	39527
2021	2	39594		29 (proj)	39623

When I try Rolling Average Quick Measure, I get this in my visual.  If I drill down to the Quarter level, the "Max of actual" line is correct, but the "actual rolling average" line doesn't appear.

If I convert the measure to a column by copy/pasting the formula to a "New Column" formula, I can see the values it's calculating aren't correct.  They're just the values from Actual.

Here is the formula being generated by the Rolling Average Quick Measure:

actual rolling average = 
IF(
	ISFILTERED('DateTable'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFQUARTER('DateTable'[Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'DateTable'[Date].[Date],
			STARTOFQUARTER(DATEADD(__LAST_DATE, -2, QUARTER)),
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('DateTable'),
					'DateTable'[Date].[Year],
					'DateTable'[Date].[QuarterNo],
					'DateTable'[Date].[Quarter]
				),
				__DATE_PERIOD
			),
			CALCULATE(
				SUM('goals'[actual]),
				ALL(
					'DateTable'[Date].[MonthNo],
					'DateTable'[Date].[Month],
					'DateTable'[Date].[Day]
				)
			)
		)
)

I feel this issue is likely just my ignorance of Power BI in general, but I don't know what question to ask.  Is it a math problem or a "learning to use Power BI" problem?  Any guidance would be greatly appreciated.

[v-jingzhang]

Hi @Anonymous 

What is the math formula to calculate Projection for rows already have actual values and those not have actual values? Or can you describe the logic to get Projection so that we can create measures for it?

Regards,
Community Support Team _ Jing Zhang

[lbendlin]

your visual has an unfortunate date hierarchy. You are calculating by quarter but your visual is locked to year. Throw that hierarchy away by declaring your own dates table in the data model.

[Anonymous]

Thanks for the answer, but I've already done that in the examples posted above, I just didn't show it.  If I drill down one level in the line chart, the Goal and Actual lines correctly show numbers by quarter, but the calculated values ("actual rolling average") don't appear correctly.  See the image below