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 ...

Get Microsoft Power BI Cookbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.