Forum Discussion

austena229022's avatar
austena229022
Frequent Visitor
1 year ago
Solved

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 tha...
Need Help
Show and Tell
Tips and Tricks
  • austena229022's avatar
    austena229022
    1 year ago

    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's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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