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])