Forum Discussion
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 ...
You can try measure below for the Rolling 3 months, it requires a Year/Month column combination in your Calendar tableSales 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
Joe_Barry
Solution Sage
Solution Sage
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