Rolling Month growth measure not working correctly

[RD_PowerBI24]

Hi All, 

I have a rolling sales column in my data set that adjust based on the selected rolling value, R3M, R6M, MAT.

However, the growth measure doesn't seem to add up. Here is the table for R3M sales & Monthly Volumes. 

Year	Month	Growth	All_Sales Rolling 3M Average	All_Sales
2023	January		202470	202470
2023	February		215690	228910
2023	March	27.96%	230155	259085
2023	April	-4.39%	235618	218860
2023	May	4.69%	249727	271235
2023	June	28.41%	257043	281035
2023	July	-5.53%	269503	256240
2023	August	-13.59%	260043	242855
2023	September	-9.09%	244012	232940
2023	October	-5.28%	235273	230025
2023	November	4.90%	235773	244355
2023	December	6.07%	239455	243985
2024	January	-11.71%	234692	215735
2024	February	2.30%	236442	249605
2024	March	11.50%	235293	240540
2024	April	2.49%	248658	255830
2024	May	-10.65%	237097	214920

This is the code for the rolling average all_sales column:

All_Sales Rolling Average =

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]))

        )

)

'Dynamic Rolling Average'[Dynamic Rolling Average Value] is a negative numeric value that changes based on what is selected to amend the time period to look back on how the sales are calculated. The code above does this correctly if you sense check using the all_sales column on far right.

The growth code is here below, again using a dynamic rolling average value to calculate the growth on different time periods.

All_Sales MoM% =

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 __PREV_MONTH =

        CALCULATE(

            [All_Sales],

            DATEADD('All_Sales'[All_Invoice_Date].[Date],'Dynamic Rolling Average'[Dynamic Rolling Average Value], MONTH)

        )

    RETURN

        DIVIDE([All_Sales] - __PREV_MONTH, __PREV_MONTH)

)

So on a R3M, this should be R3M May - R3M Feb/R3M Feb. (237097 - 236442)/236442 = 0.28%. Instead I get -10.65% and all sorts of other percentages in the table. Please can you let me know what is to be done here?

Thank you in advance.

[v-shex-msft]

Hi @RD_PowerBI24 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[RD_PowerBI24]

Hi Xiaoxin Sheng, It didn't as I had already a calendar and date dimensions setup. I found another solution which helped specific to my situation.

[parry2k]

@RD_PowerBI24 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

Replace all the date column in your measures to date column from the date dimension and then test it.

[RD_PowerBI24]

Thank you. Will take a look.