cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## Quick Measure: Rolling Average per year Help!

Hello!,

I need help, i'm using quick measure rolling average and it's calculating correctly but i want to use Year filter, i don't want it to calculate the rolling average taking the average from the prior year, i want it to start the measure for year, so when i calculate it, the result should be like this:

 year month x measure x rolling average 2017 Jan 71% 71% 2017 Feb 64% 68% 2017 Mar 72% 69% 2017 Apr 72% 70% 2017 May 77% 71% 2017 Jun 75% 72% 2017 Jul 73% 72% 2017 Aug 76% 73% 2017 Sep 68% 72% 2017 Oct 66% 72% 2017 Nov 66% 71% 2017 Dic 66% 71% 2018 Jan 59% 59%

As you can see in 2018 the rolling average should start calculating without taking in consideration the accumulated of 2017, every year should be like this.

The quick measure dax formula is:

x rolling average =

IF(
ISFILTERED('NPS'[Fecha]),
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('NPS'[Fecha].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'NPS'[Fecha].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -COUNTA(NPS[Fecha]), MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('NPS'),
'NPS'[Fecha].[Year],
'NPS'[Fecha].[QuarterNo],
'NPS'[Fecha].[Quarter],
'NPS'[Fecha].[MonthNo],
'NPS'[Fecha].[Month]
),
__DATE_PERIOD
),
CALCULATE([x], ALL('NPS'[Fecha].[Day]))
)
)

i don't know exactly what should i change in the formula or if there's another way.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Quick Measure: Rolling Average per year Help!

Hi @avcr29

What happens if you try this

```x rolling average =

IF(
ISFILTERED('NPS'[Fecha]),
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('NPS'[Fecha].[Date])
VAR __DATE_PERIOD =
FILTER(
DATESBETWEEN(
'NPS'[Fecha].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -COUNTA(NPS[Fecha]), MONTH)),
__LAST_DATE
),YEAR(__LAST_DATE) = YEAR( 'NPS'[Fecha].[Date]))
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('NPS'),
'NPS'[Fecha].[Year],
'NPS'[Fecha].[QuarterNo],
'NPS'[Fecha].[Quarter],
'NPS'[Fecha].[MonthNo],
'NPS'[Fecha].[Month]
),
__DATE_PERIOD
),
CALCULATE([x], ALL('NPS'[Fecha].[Day]))
)
)```

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Quick Measure: Rolling Average per year Help!

Hi @avcr29

What happens if you try this

```x rolling average =

IF(
ISFILTERED('NPS'[Fecha]),
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('NPS'[Fecha].[Date])
VAR __DATE_PERIOD =
FILTER(
DATESBETWEEN(
'NPS'[Fecha].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -COUNTA(NPS[Fecha]), MONTH)),
__LAST_DATE
),YEAR(__LAST_DATE) = YEAR( 'NPS'[Fecha].[Date]))
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('NPS'),
'NPS'[Fecha].[Year],
'NPS'[Fecha].[QuarterNo],
'NPS'[Fecha].[Quarter],
'NPS'[Fecha].[MonthNo],
'NPS'[Fecha].[Month]
),
__DATE_PERIOD
),
CALCULATE([x], ALL('NPS'[Fecha].[Day]))
)
)```

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Frequent Visitor

## Re: Quick Measure: Rolling Average per year Help!

Thank you so much @Phil_Seamark, it works! that's what i was looking for.