Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated Forecast Based on Previous YOY growth

I need to create a forecast based on % growth from previous years. Below is my Excel table, I want this to be automatically calculated each month in PowerBI instead of having to manually update in Excel every month. I am having trouble in figuring out how to calculate the last two columns in Power BI and getting the future years to show up as well. Any help would be appreciated. 

 

Year                    Sales                        YOY Growth percent   Forecast based on 19% Growth   Forecast based on 16% growth

2016$6,553,524.60   
2017$7,571,281.700.16  
2018$8,999,105.400.19  
2019$10,069,232.560.12  
2020$11,933,395.440.19  
2021  $14,142,679.28$13,866,404.83
2022  $16,760,977.91$16,112,529.22
2023  $19,864,014.08$18,722,488.01
2024  $23,541,529.47$21,755,216.22
     
 Average growth percent in past years0.16  
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Try measure like this :

    Forecast based on 16% growth =
VAR current_year =
    YEAR ( TODAY () )
VAR lastest_year =
    MAX ( 'Table (2)'[Year ] )
VAR avg_growth = [AVG_Growth]
VAR last_year_sales =
    CALCULATE ( LASTNONBLANK ( 'Table (2)'[Sales], 1 ), ALL ( 'Table (2)' ) )
RETURN
    last_year_sales
        * CALCULATE (
            PRODUCTX ( 'Table (2)', 1 + avg_growth ),
            FILTER (
                ALL ( 'Table (2)' ),
                'Table (2)'[Year ] <= lastest_year
                    && (
                        ISBLANK ( 'Table (2)'[Sales] )
                            && 'Table (2)'[Year ] > current_year
                    )
            )
        )

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try with a date of year table

     

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
YOY = divide([This Year]-[Last Year],[Last Year])

Future= 
var _last = lastnonblankvalue('Date'[year],[YOY])
var _lastv = lastnonblankvalue('Date'[year],[This Year])
var _lasty = calculate(Max('Date'[Year]),filter(Table,not(isblank(Table[Sales]))))
return
calculate(_lastv*power(_last,max('Date'[year])-_last), filter(Date[year] =maxx(Date[year])))
  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Try measure like this :

    Forecast based on 16% growth =
VAR current_year =
    YEAR ( TODAY () )
VAR lastest_year =
    MAX ( 'Table (2)'[Year ] )
VAR avg_growth = [AVG_Growth]
VAR last_year_sales =
    CALCULATE ( LASTNONBLANK ( 'Table (2)'[Sales], 1 ), ALL ( 'Table (2)' ) )
RETURN
    last_year_sales
        * CALCULATE (
            PRODUCTX ( 'Table (2)', 1 + avg_growth ),
            FILTER (
                ALL ( 'Table (2)' ),
                'Table (2)'[Year ] <= lastest_year
                    && (
                        ISBLANK ( 'Table (2)'[Sales] )
                            && 'Table (2)'[Year ] > current_year
                    )
            )
        )

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.