Forum Discussion

MrMarshall's avatar
MrMarshall
Icon for Helper II rankHelper II
7 years ago

Quick measure rolling average with Date-filter

I am trying to use the built in quick measure Rolling average LTM with a date slicer to filter the visualization.

I have data from 2014-2017 in this supersimple example pbix you can download from here taht exists of a Sales table and a Data table.

 

Without the Date-slicer "Year-Month", the data is correct. But when filtering the visualization, the computed data also gets filtered as you can see in the screenshot. The "first" year gets affected. 


I have used the built in quick measure rolling average as you can see below, and I really want to keep it that way. But how do I calculate outside of the filtered data?

 

 

Sales rolling average = 
IF(
	ISFILTERED('Calendar'[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 = ENDOFMONTH('Calendar'[Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'Calendar'[Date].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, -11 , MONTH)),
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('Calendar'),
					'Calendar'[Date].[Year],
					'Calendar'[Date].[QuarterNo],
					'Calendar'[Date].[Quarter],
					'Calendar'[Date].[MonthNo],
					'Calendar'[Date].[Month]
				),
				__DATE_PERIOD
			),
			CALCULATE(SUM('Salestable'[Sales]), ALL('Calendar'[Date].[Day]))
		)
)
Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi MrMarshall 

    Do you mean 

    For year=2016, month=1, "sales rolling average" should be equal to

    {(total sales before 2016)+(sales in 2016/01)}

    /

    numbers of month(? if so, which months should be considered?)

     

    Best Regards

    Maggie

    • MrMarshall's avatar
      MrMarshall
      Icon for Helper II rankHelper II
      7 years ago

      Hi v-juanli-msft !

      Thanks for the reply.

      For year=2016, month=1, "sales rolling average" should be calculated with data from one year back. No matter what my Slicer shows.

       

      Which means, for year=2016, month=1, I would still like to see an average of 12 month back, even if I have no slicer values from 2015 chosen. : 

      (Sales from 2015/01 to 2016/01) / 12 month.

       

      So the measure should ignore my slicer.
      That way, I can look on JUST 2016 on the visualization, but the rolling average is based on data from 2015 aswell.