Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

month over month change for Rolling 12 months

I want to show a visual which return month over month change for last 12 months. Can someone provide the dax code for it

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous,

Did you mean the expression should apply to the last 12-month ranges based on the latest date and show the month-over-month difference in these ranges?

For your requirement, you can add variables to extract value from the table and use the if statement to check the current date and the last date and skip the calculation in the not match date ranges.

Sample measure:

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR cMonth =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = currDate
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )
VAR pMonth =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = prevDate
                && MONTH ( [Date] ) = MONTH ( prevDate )
        )
    )
VAR _lastDate =
    MAXX ( ALLSELECTED ( Table ), [Date] )
VAR _LYDate =
    DATE ( YEAR ( _lastDate ) - 1, MONTH ( _lastDate ), DAY ( _lastDate ) )
RETURN
    IF (
        currDate >= _LYDate
            && currDate <= _lastDate,
        DIVIDE ( cMonth - pMonth, pMonth )
    )

Regards,
Xiaoxin Sheng

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , With help from a date tbale used in formula and visual

a measure

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

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Thanks for the reply. I want to calculate month over month change and show it for last 12 months. I want to calculate the change percent in current month value as compared to previous month value and return that change for last 12 months

Anonymous
Not applicable

Hi @Anonymous,

Did you mean the expression should apply to the last 12-month ranges based on the latest date and show the month-over-month difference in these ranges?

For your requirement, you can add variables to extract value from the table and use the if statement to check the current date and the last date and skip the calculation in the not match date ranges.

Sample measure:

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR cMonth =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = currDate
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )
VAR pMonth =
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = prevDate
                && MONTH ( [Date] ) = MONTH ( prevDate )
        )
    )
VAR _lastDate =
    MAXX ( ALLSELECTED ( Table ), [Date] )
VAR _LYDate =
    DATE ( YEAR ( _lastDate ) - 1, MONTH ( _lastDate ), DAY ( _lastDate ) )
RETURN
    IF (
        currDate >= _LYDate
            && currDate <= _lastDate,
        DIVIDE ( cMonth - pMonth, pMonth )
    )

Regards,
Xiaoxin Sheng

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.