Last 12 Month Rolling Avg MoM %

[Bhanuaripaka]

🧮 Step-by-Step MoM Growth Calculation (for each month)

MoM Growth=Current Month−Previous MonthPrevious Month\text{MoM Growth} = \frac{\text{Current Month} - \text{Previous Month}}{\text{Previous Month}}MoM Growth=Previous MonthCurrent Month−Previous Month​

Let’s calculate this for the 12 months from Feb 2024 to Jan 2025:

Month Active Count Prev Month Count MoM Growth (%)

Feb 2024	1,100
Mar 2024	1,050	1,100	-4.55%
Apr 2024	1,200	1,050	14.29%
May 2024	1,300	1,200	8.33%
Jun 2024	1,250	1,300	-3.85%
Jul 2024	1,400	1,250	12.00%
Aug 2024	1,500	1,400	7.14%
Sep 2024	1,600	1,500	6.67%
Oct 2024	1,550	1,600	-3.13%
Nov 2024	1,650	1,550	6.45%
Dec 2024	1,700	1,650	3.03%
Jan 2025	1,800	1,700	5.88%

 

---

📈 Rolling 12-Month MoM Growth

Take the average of the last 12 MoM Growth values from Feb 2024 to Jan 2025: As Feb is blank dax should calculate 11 months

Rolling MoM Avg = 10.00 - 4.55 + 14.29 + 8.33 - 3.85 + 12.00 + 7.14 + 6.67 - 3.13 + 6.45 + 3.03 + 5.88/11 = ???(Result)  

Rolling 12-Month MoM Avg Growth for Feb 2025 result will be Avg of Mar 2024 to Jan 2025 MoM% growth. 

 

Can you please some one help me with the dax logic for the output I am after. 

 

Apprecaite your help.  

[v-pgoloju]

Hi @Bhanuaripaka,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful  this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[Ilgar_Zarbali · Accepted solution]

You need a table that includes:

• A Date column, ideally showing the first day of each month
• A column for Active Count

Important notes:

• Ensure your Date table is continuous (no missing months) and is marked as a date table in Power BI.
• Replace 'YourTable', Active Count, and Date with the actual table and column names in your model.
• Also, confirm that your Date table is linked to your main data table properly.

The following DAX Formula gives you the month-over-month % change for each month.

MoM Growth % = 
VAR CurrentMonthValue = CALCULATE(SUM('YourTable'[Active Count]))
VAR PrevMonthValue = 
    CALCULATE(
        SUM('YourTable'[Active Count]),
        DATEADD('Date'[Date], -1, MONTH)
    )
RETURN
IF(
    NOT ISBLANK(PrevMonthValue),
    DIVIDE(CurrentMonthValue - PrevMonthValue, PrevMonthValue)
)

For February 2025, it will average the MoM growth from March 2024 to January 2025 (i.e., the previous 11 months, excluding Feb 2024 since it has no previous month).

Rolling 12-Month MoM Avg % =
VAR CurrentDate = MAX('Date'[Date])
VAR MoMTable =
    ADDCOLUMNS(
        DATESINPERIOD('Date'[Date], CurrentDate, -12, MONTH),
        "MoMGrowth", 
            CALCULATE(
                [MoM Growth %]
            )
    )
VAR FilteredMoM =
    FILTER(MoMTable, NOT(ISBLANK([MoMGrowth])))
RETURN
AVERAGEX(FilteredMoM, [MoMGrowth])

If this solution works for you, please consider marking it as accepted so it can help others too!

[v-pgoloju]

Hi @Bhanuaripaka,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Try this dax measure

Rolling 12M MoM Avg % =
VAR CurrentMonth = MAX(Activity[Month])
VAR Last12Months =
DATESINPERIOD(Activity[Month], CurrentMonth, -12, MONTH)

VAR MoMTable =
ADDCOLUMNS(FILTER(ALL(Activity),Activity[Month] IN Last12Months),"PrevCount",
CALCULATE(SUM(Activity[Active Count]),DATEADD(Activity[Month], -1, MONTH)),
"MoM%",
DIVIDE(SUM(Activity[Active Count]) -
CALCULATE(SUM(Activity[Active Count]), DATEADD(Activity[Month], -1, MONTH)),
CALCULATE(SUM(Activity[Active Count]), DATEADD(Activity[Month], -1, MONTH))))

VAR Result =AVERAGEX(FILTER(MoMTable,NOT ISBLANK([MoM%])),[MoM%])

RETURN
Result

If this solution helped, please consider marking the response as accepted and giving it a thumbs-up so others can benefit as well.

Best regards,
Prasanna Kumar