Creating Forecast Line for next 12 Months based on past average

[austena229022]

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

[austena229022 · Accepted solution]

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)/2

    VAR 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, "")

    RETURN

    output

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...