Calculating Month over Month Change of a 12-Month Rolling Sum

[Anonymous]

Hello,

I am attempting to calculate the monthly percent change for a rolling 12-month sum. I tried using two quick measures, rolling average (which was changed to sum) and month over month change. However, the month over month change is incorrect for all but the first row. How do I get accurate numbers of month over month change? I'm new to PowerBI and do not have much experience with DAX. 

Below are my measures:

EBITDAE rolling sum MoM% =

IF(

    ISFILTERED('div_dept_EBITDAE'[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(

            [EBITDAE rolling sum],

            DATEADD('div_dept_EBITDAE'[Date].[Date], -1, MONTH)

        )

    RETURN

        DIVIDE([EBITDAE rolling sum] - __PREV_MONTH, __PREV_MONTH)

)

EBITDAE rolling sum =

IF(

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

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'div_dept_EBITDAE'[Date].[Date],

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

            __LAST_DATE

        )

    RETURN

        SUMX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('div_dept_EBITDAE'),

                    'div_dept_EBITDAE'[Date].[Year],

                    'div_dept_EBITDAE'[Date].[QuarterNo],

                    'div_dept_EBITDAE'[Date].[Quarter],

                    'div_dept_EBITDAE'[Date].[MonthNo],

                    'div_dept_EBITDAE'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(

                SUM('div_dept_EBITDAE'[EBITDAE]),

                ALL('div_dept_EBITDAE'[Date].[Day])

            )

        )

)

[v-eachen-msft]

Hi @Anonymous ,

Could you please share your sample data and the expected result here if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

Here is a similar case for your reference.

https://community.powerbi.com/t5/Desktop/Rolling-12-months-data/m-p/1088093

[amitchandak]

@Anonymous , Try with a date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.