Forum Discussion
Creating Forecast Line for next 12 Months based on past average
I have created a graph with applications received in the past 36 months with an average line for the passt 24 months.
What I am wanting to do is to have a forecast line for the next 12 months that takes the average of each month for the past 36 months to plot on the line graph. eg: take the average of Aug 22, Aug 23 & Aug 24 and plot that for Aug 25
The data mode is simple tables counting rows, with a relationship to a dat table. Eg each application is a row in the table
I have worked on it and found the solution:
VAR twm =CALCULATE(COUNTROWS('Applications w/ Filter'),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received] >= DATE(YEAR(MIN('Date Table'[date_as_date]))-1, MONTH(MIN('Date Table'[date_as_date])), 1)),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received] <= DATE(YEAR(MAX('Date Table'[date_as_date]))-1, MONTH(MAX('Date Table'[date_as_date])), DAY(MAX('Date Table'[date_as_date])))))VAR tfm =CALCULATE(COUNTROWS('Applications w/ Filter'),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received] >= DATE(YEAR(MIN('Date Table'[date_as_date]))-2, MONTH(MIN('Date Table'[date_as_date])), 1)),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received]<= DATE(YEAR(MAX('Date Table'[date_as_date]))-2, MONTH(MAX('Date Table'[date_as_date])), DAY(MAX('Date Table'[date_as_date])))))VAR calcav =(twm + tfm)/2VAR output =IF(AND(DATE(YEAR(MIN('Date Table'[date_as_date])), MONTH(MIN('Date Table'[date_as_date]))+1, 1) > DATE(YEAR(TODAY()), MONTH(TODAY()), 1),DATE(YEAR(MAX('Date Table'[date_as_date])), MONTH(MAX('Date Table'[date_as_date])), DAY(MAX('Date Table'[date_as_date]))) < DATE(YEAR(TODAY()), MONTH(TODAY())+11, DAY(MAX('Date Table'[date_as_date])))) , calcav, "")RETURNoutput
2 Replies
- lbendlin
Super User
use LINESTX, like they use for the built-in forecasting option.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 I have worked on it and found the solution:
VAR twm =CALCULATE(COUNTROWS('Applications w/ Filter'),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received] >= DATE(YEAR(MIN('Date Table'[date_as_date]))-1, MONTH(MIN('Date Table'[date_as_date])), 1)),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received] <= DATE(YEAR(MAX('Date Table'[date_as_date]))-1, MONTH(MAX('Date Table'[date_as_date])), DAY(MAX('Date Table'[date_as_date])))))VAR tfm =CALCULATE(COUNTROWS('Applications w/ Filter'),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received] >= DATE(YEAR(MIN('Date Table'[date_as_date]))-2, MONTH(MIN('Date Table'[date_as_date])), 1)),FILTER('Applications w/ Filter', 'Applications w/ Filter'[Date Received]<= DATE(YEAR(MAX('Date Table'[date_as_date]))-2, MONTH(MAX('Date Table'[date_as_date])), DAY(MAX('Date Table'[date_as_date])))))VAR calcav =(twm + tfm)/2VAR output =IF(AND(DATE(YEAR(MIN('Date Table'[date_as_date])), MONTH(MIN('Date Table'[date_as_date]))+1, 1) > DATE(YEAR(TODAY()), MONTH(TODAY()), 1),DATE(YEAR(MAX('Date Table'[date_as_date])), MONTH(MAX('Date Table'[date_as_date])), DAY(MAX('Date Table'[date_as_date]))) < DATE(YEAR(TODAY()), MONTH(TODAY())+11, DAY(MAX('Date Table'[date_as_date])))) , calcav, "")RETURNoutput
