Forum Discussion

avcr29's avatar
avcr29
Frequent Visitor
8 years ago
Solved

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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]))
)
)

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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]))
)
)

 

avcr29's avatar
avcr29
Frequent Visitor
8 years ago

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