Forum Discussion
rolling 12 months average quick measure
i generated quick measure for "rolling 12 months average sales" however it is not matching with actual measure. please see below pbix file
https://drive.google.com/file/d/1zuTSZE_dw6qJ5CcNi4MdDOH-EdEsEIwO/view?usp=drive_link
Hi powerbiexpert22 -The main difference in auto generated quick measure, it is using the DATESBETWEEN with DATEADD to get a 12-month range but adds an extra month (ENDOFMONTH(DATEADD(__LAST_DATE, 1, MONTH))) where as in custom measure Uses DATEPERIOD function to get a 12-month range directly. one more difference found at the aggregation that is calculated at quick measure,it uses averagex with a summarized table, which might include additional months and hence affect the result where as in custom measure it uses the sums the sales amount and then divides by 12 to get the average.
Once quick measure generated for rolling average, you can modify to use Dateperiod function and avoid adding the extra month.
Modified quick measure:
Sales rolling average =
IF(
ISFILTERED('DateDim'[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(MAX('DateDim'[Date]))
VAR __DATE_PERIOD =
DATESINPERIOD(
'DateDim'[Date],
__LAST_DATE,
-12,
MONTH
)
RETURN
CALCULATE(
SUM(SalesFact[amount]),
__DATE_PERIOD
) / 12
)Hope it gives you the same result like custom measure
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
2 Replies
Hi powerbiexpert22 -The main difference in auto generated quick measure, it is using the DATESBETWEEN with DATEADD to get a 12-month range but adds an extra month (ENDOFMONTH(DATEADD(__LAST_DATE, 1, MONTH))) where as in custom measure Uses DATEPERIOD function to get a 12-month range directly. one more difference found at the aggregation that is calculated at quick measure,it uses averagex with a summarized table, which might include additional months and hence affect the result where as in custom measure it uses the sums the sales amount and then divides by 12 to get the average.
Once quick measure generated for rolling average, you can modify to use Dateperiod function and avoid adding the extra month.
Modified quick measure:
Sales rolling average =
IF(
ISFILTERED('DateDim'[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(MAX('DateDim'[Date]))
VAR __DATE_PERIOD =
DATESINPERIOD(
'DateDim'[Date],
__LAST_DATE,
-12,
MONTH
)
RETURN
CALCULATE(
SUM(SalesFact[amount]),
__DATE_PERIOD
) / 12
)Hope it gives you the same result like custom measure
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Hi,
I think you will get the correct answer with this measure
Measure = AVERAGEX(SUMMARIZE(CALCULATETABLE(DateDim,DATESBETWEEN(DateDim[Date],EDATE(MIN(DateDim[Date]),-11),MAX(DateDim[Date]))),DateDim[Year],DateDim[Month],"A",[Sales]),[A])Hope this helps.
