Rolling Average vs Same last period Rolling average

[mehtatanish34]

Hello,

I’m tasked to compare rolling averages of percentage growth over the last 3 months with the same period rolling average of 3 months from the previous year, but I’m having some issues with my DAX calculations. Here are the measures I’ve written, but the results aren’t as expected.
I’d appreciate any help.
Monthly Sales Growth (%) = VAR CurrentMonthSales = [Total Sales]
VAR PreviousMonthSales =CALCULATE([Total Sales],DATEADD(Calendar[Date], -1, MONTH))
RETURN
DIVIDE(CurrentMonthSales - PreviousMonthSales,PreviousMonthSales,0) * 100

Rolling Avg Growth (3 Months) =
AVERAGEX(DATESINPERIOD(Calendar[Date],MIN(Calendar[Date]),-3,MONTH),[Monthly Sales Growth (%)] )

Rolling Avg Growth Last Year (3 Months) =
CALCULATE([Rolling Avg Growth (3 Months)],DATEADD(Calendar[Date], -12, MONTH))

Comparison of Rolling Avg Growth =
VAR CurrentRollingAvg = [Rolling Avg Growth (3 Months)]
VAR PreviousYearRollingAvg = [Rolling Avg Growth Last Year (3 Months)]
RETURN
CurrentRollingAvg - PreviousYearRollingAvg

[Joe_Barry · Accepted solution]

Hi @mehtatanish34 

You can try measure below for the Rolling 3 months, it requires a Year/Month column combination in your Calendar table

Sales R3M =
VAR NumOfMonths = 3
VAR LastCurrentDate =
    MAX ( 'Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Calendar'[Calendar Year Month] ),
            [Total Sales]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Calendar'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] ) ///Enter the date that you want to calculate on
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

To get the previous year, try this

Sales R3M PY =
CALCULATE(
  [Sales R3M], 
    DATEADD('Calendar'[Date], -1 , YEAR)

For the comparison just keep it simple

Sales R3M PY Diff =
[Sales R3M] - [Sales R3M PY]

If you are looking Year on Year

YoY =
DIVIDE([Sales R3M PY Diff], [Sales R3M P]

Kudos to https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Joe