Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
avcr29
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: 

 

yearmonthx measurex rolling average 
2017Jan71%71%
2017Feb64%68%
2017Mar72%69%
2017Apr72%70%
2017May77%71%
2017Jun75%72%
2017Jul73%72%
2017Aug76%73%
2017Sep68%72%
2017Oct66%72%
2017Nov66%71%
2017Dic66%71%
2018Jan59%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
Phil_Seamark
Employee
Employee

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!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

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!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.