Rolling 12 month average

[Madhatter]

Hi,

I used the quick measure to create a 12 month rolling average and selected 0 for period after but the quick measure is going up to December of 2023, I would just like it to look at the preceding 12 months, is this possible?

ChangeTicketsCcreatedRA12 =

IF(

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

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'Query1'[Date].[Date],

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

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('Query1'),

                    'Query1'[Date].[Year],

                    'Query1'[Date].[QuarterNo],

                    'Query1'[Date].[Quarter],

                    'Query1'[Date].[MonthNo],

                    'Query1'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(SUM('Query1'[ChangeTicketsCreated]), ALL('Query1'[Date].[Day]))

        )

)

[amitchandak]

@Madhatter , You should use a date table and measure like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

you can also consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc