Previous Rolling Average from Rolling Average Quick Measure

[RD_PowerBI24]

Hi,

I've created a Rolling Average on sales using the quick measure feature on my date column.

I was given the pre-defined DAX code and I amended the intervals for DateAdd function so that it is dynamic and changes based on whether we look back 3, 6 or 12 months as an average using "Dynamic Rolling Average Value" as per below:

Rolling Average All_Sales =

IF(

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

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'All_Sales'[All_Invoice_Date].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, 'Dynamic Rolling Average'[Dynamic Rolling Average Value], MONTH)),

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('All_Sales'),

                    'All_Sales'[All_Invoice_Date].[Year],

                    'All_Sales'[All_Invoice_Date].[QuarterNo],

                    'All_Sales'[All_Invoice_Date].[Quarter],

                    'All_Sales'[All_Invoice_Date].[MonthNo],

                    'All_Sales'[All_Invoice_Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE([All_Sales], ALL('All_Sales'[All_Invoice_Date].[Day]))

        )

)

I am stuck on creating the previous rolling average which simply needs to be the same calculated rolling average but brought forward from previous months, depending on the dynamic selection of the time period. E.g. below is the output table I would desire ("Previous..." header columns is the measure to be created:

Can someone guide how to do to this, as I am stuck after various iterations to attempt to get this?

Month	R3M	Previous R3M	R6M	Previous R6M
Jan-23	202470		202470
Feb-23	215690		215690
Mar-23	230155		230155
Apr-23	235618	202470	227331
May-23	249727	215690	236112
Jun-23	257043	230155	243599
Jul-23	269503	235618	252561	202470
Aug-23	260043	249727	254885	215690
Sep-23	244012	257043	250528	230155
Oct-23	235273	269503	252388	227331
Nov-23	235773	260043	247908	236112
Dec-23	239455	244012	241733	243599
Jan-24	234692	235273	234983	252561
Feb-24	236442	235773	236108	254885
Mar-24	235293	239455	237374	250528
Apr-24	248658	234692	241675	252388
May-24	237097	236442	236769	247908

[Anonymous · Accepted solution]

Hi @RD_PowerBI24 

Based on your description, you need to create a calendar table first.

Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,5,1))

Then create a relationship between data table and calendar table.

Then create a measure

PreRM =
CALCULATE (
    [Rolling Average All_Sales],
    DATEADD (
        'Calendar'[Date],
        - MAX ( 'Dynamic Rolling Average'[Dynamic Rolling Average Value] ),
        MONTH
    )
)

Then put the date column of calendar table  and the measure to the visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.