Time Series Forecasting in Power BI
First, let’s create the DetrendedRevenue measure:
DetrendedRevenue = [Revenue] - [Sales Forecast - Trend]
Now we can create the Seasonality measure:
Sales Forecast - Seasonality =
VAR CurrentYear = MAX('Date'[Year])
VAR CurrentWeek = MAX('Date'[Week])
VAR PriorYears =
FILTER(
SUMMARIZE(
FILTER(
ALL('Date'),
'Date'[Year] <= CurrentYear
&& 'Date'[Week] = CurrentWeek
),
'Date'[Year],
'Date'[Week],
"_Sales", [DetrendedRevenue], "_originalSales", [Revenue]
),
[_originalSales]<> BLANK())
RETURN
AVERAGEX(PriorYears, [_Sales])