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
austena229022's avatar
austena229022
Frequent Visitor
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