Forum Discussion

mehtatanish34's avatar
mehtatanish34
New Member
2 years ago
Solved

Rolling Average vs Same last period Rolling average

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's avatar
    Joe_Barry
    2 years ago

    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

1 Reply

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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