Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Need Rolling Average But Quick Measure Not Working

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:

YearQuarterGoalActualDeltaProjection (-2 Q)
202013907938942-137 
202023914939049-100 
202033919939040-15939080
2020439399394545539402
2021139524 3 (proj)39527
2021239594 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.

3 Replies

  • 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's avatar
      Anonymous
      Not applicable

      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

       

  • v-jingzhang's avatar
    v-jingzhang
    Community Support

    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