rolling 12 months average quick measure

[powerbiexpert22]

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

without quick measure

rolling12maverage =

CALCULATE(

    SUM(SalesFact[amount]),

    DATESINPERIOD(DateDim[Date],max(DateDim[Date]),-12,MONTH)

)/12

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('DateDim'[Date].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'DateDim'[Date].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),

            ENDOFMONTH(DATEADD(__LAST_DATE, 1, MONTH))

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('DateDim'),

                    'DateDim'[Date].[Year],

                    'DateDim'[Date].[QuarterNo],

                    'DateDim'[Date].[Quarter],

                    'DateDim'[Date].[MonthNo],

                    'DateDim'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE([Sales], ALL('DateDim'[Date].[Day]))

        )

)

[Ashish_Mathur]

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.

[rajendraongole1 · Accepted solution]

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!!