Forum Discussion
What quick measure is that?
Hello
I've encounter such measure and I am not sure which one is that. I can see its not rolling average cause it computes another way. It claims to be 12 months rolling average. What this code does? calculates average from 12 months from datastamp (month-year lets say), so for instance on May 2022 it will give result of 12 months average value? or what
IF(
ISFILTERED('Fact Table'[Period]),
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('Fact Table'[Period].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Fact Table'[Period].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Fact Table'),
'Fact Table'[Period].[Year],
'Fact Table'[Period].[QuarterNo],
'Fact Table'[Period].[Quarter],
'Fact Table'[Period].[MonthNo],
'Fact Table'[Period].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Fact Table'[Value]),
ALL('Fact Table'[Period].[Day])
)
)
)
2 Replies
- olgad
Resident Rockstar
Hi,
it is 12 month rolling average.If you look at Feb 2023 then you see the sum of the last 12 months divided by 12.
- Pbiuserr
Post Prodigy
Why quick measures produce another result when I try to recreate rolling average?
how to establish such measure with traditional calendar table? Like that?