Microsoft Power BI Cookbook

Apply the forecast to historical values

1. Develop a dynamic Sales by Month query to be used by the forecast variables. The query must use the current year history if it's available or, if the month isn't completed in the current year, use the prior year value.
2. To simplify this query, create a PriorYearMonthlySales query and a CurrentYearMonthlySalesQuery:

Current and Prior Year Queries used by the PrimarySalesForecastBase query

The following expression of the PriorYearMonthlySales query only retrieves the prior year months that haven't been completed in the current year:

let CurrentYear = Date.Year(DateTime.Date(DateTime.LocalNow())),CurrentMonth ...