Rolling Average that works with a custom date hierarchy

[mlapu]

Hi, 

I am building a visual that shows the amount of time my piece of equipment runs each week (in hours). Each row of the data is 15 minutes, so there are 4 rows per hour.

If the equipment is running for that interval, the value is 1, if it is shut down, the value is zero.

I have the graph built using a custom date hierarchy:

Now I would like to add a rolling average to the graph. I can't use the Rolling Average Quick Measure because the the hierarchy isn't recognized as a date hierarchy (with the little calendar icon).  Anyone have thoughts on how I can implement a rolling average to this data? 

[v-kkf-msft · Accepted solution]

Hi @mlapu ,

I made some changes to the quick measure formula so that it calculates the weekly average rolled back to the past two months.

rolling average = 
VAR __LAST_DATE = ENDOFMONTH('Date'[Date])
VAR __DATE_PERIOD =
	DATESBETWEEN(
		'Date'[Date],
		STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)),
		__LAST_DATE
	)
RETURN
	AVERAGEX(
		CALCULATETABLE(
			SUMMARIZE(
				VALUES('Date'),
				'Date'[Year],
				'Date'[Month Name],
				'Date'[Week of Month]
			),
			__DATE_PERIOD
		),
		CALCULATE(
			SUM('Table'[A041DC01SCRBR_STARTUP_CMPL_VAL0]),
			ALL('Date'[Date])
		)
	)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlapu]

Hi Winniz,

Thank you for your time and effort! Unfortunately there is an error:

I have gotten this error a lot while working with this data, and has forced me to make some creative adjustments to my code at times. Here is a snap shot of the Date column:

Any thoughts on how I can bypass this error?

Thanks again for the help!

Mark

[v-kkf-msft]

Hi @mlapu ,

When referencing the date column in the DATESBETWEEN function, the same date cannot have different timestamps. So you cannot use the timestamp column in the DATESBETWEEN function, you need to create a new date column in the table.

Date = 'Table'[timestamp].[Date]

Or

Date = DATE(YEAR('Table'[timestamp]), MONTH('Table'[timestamp]), DAY('Table'[timestamp]))

And if you have associated Date table, you need to modify the relationship between the tables to: the Date column in Date -- the Date column in Table.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.