Forecasting with Parameters from Historical Data

[datagamble43]

Hi all, 

Ultimatley, I am trying to create and interactive forecasting model from hisotrical data. Within this, I want users to be able to dynamically change growth factors that apply to different categorical levels, year over year (building off of the last year of actual data, so the 2023 forecast would be (2022 data) * (1+ *insert growth factors*), then 2024 forecast would be (2023 data) * (1+ *insert growth factors*), and so on. 

I have created some dummy data for privacy reasons, hopefully it helps. Here is the BI file that I loaded in and created parameters with, as well as the excel file which contains the expected outcome on sheet:

BI FILE

https://1drv.ms/u/s!Aq06tAa7wTh3in7lcaaBRwjc0wJZ?e=KtdQ9I 

EXCEL FILE

https://1drv.ms/x/s!Aq06tAa7wTh3in0FAXge_jmvKTo8?e=PIGC4y 

I have this built in excel but need it working in powerbi to publish for a dashboard. I have also built it on the 'back end' of power bi, using power query parameters, but need users on the front end to utilize the tool through what if parameters and slicers.

Within excel, the data being pulled in is below. When the gray box percents change, so does the chart, showing the growth by category and department and its effect. 

In powerbi, I have the data pulled in and the parameters built. I cannot seem to get Year over Year Growth however. All of the data for future years is the same value (the 2022 data). I have tried numerous nested switch statements and feel like I am overcomplicating it 🙂 Any help would be appreciated. Once I can get going with the year over year, I feel that adding in the second set of growth parameters won't be difficult. My code is below:

value with growth = IF(YEAR(MIN('Sheet3'[Year])) >= YEAR(TODAY()),

SWITCH (

    TRUE(),

    MAX('Sheet3'[Department]) = "Business", SUM('Sheet3'[Value])*(1+'Business'[Business Value]),

    MAX('Sheet3'[Department]) = "Science", SUM('Sheet3'[Value])*(1+'Science'[Science Value]),

    MAX('Sheet3'[Department]) = "Technology", SUM('Sheet3'[Value])*(1+'Technology'[Technology Value]),

    MAX('Sheet3'[Department]) = "Math", SUM('Sheet3'[Value])*(1+'Math'[Math Value]),

    MAX('Sheet3'[Department]) = "History", SUM('Sheet3'[Value])*(1+'History'[History Value])),

    SUM('Sheet3'[Value]))

Any help is appreciated and will give kudos. Thank you so much!

[bolfri · Accepted solution]

Step 1) Change the relationship so Calendar table will filter the fact_table.

Step 2) Create DateType column in Calendar table that holds an information if it's a forecast or actual value.

DataType = IF(CONTAINS(fact_table,fact_table[Year],[Year]),"Actual","Forecast")

 

Step 3) Create a measure to calculate Growth Rate for future years based on slicers.

Growth Rate - Dynamic = 
VAR __lastDateWithActual = CALCULATE(MAX('Calendar'[Year]),'Calendar'[DataType]="Actual")
VAR __thisyear = MAX('Calendar'[Year])
VAR __GrowthRatePerDepartment = 
    CALCULATE(
        AVERAGEX(
            VALUES(fact_table[Department]),
            SWITCH(fact_table[Department],
            "Business",Business[Business Value],
            "Science",Science[Science Value],
            "Technology",Technology[Technology Value],
            "Math",Math[Math Value],
            "History",History[History Value],
            1)
        ),
        ALL('Calendar')
    )
VAR __compundGrowRate = 
    CALCULATE(
        PRODUCTX(VALUES('Calendar'[Year]), 1 + __GrowthRatePerDepartment),
        'Calendar'[Year] <= __thisyear,
        'Calendar'[Year] > __lastDateWithActual
    )
RETURN __compundGrowRate 

Step 4) Create a measure that calculates value with Growth Rate:

Value with Growth Rate = var __lastActual = CALCULATE(LASTNONBLANKVALUE('Calendar'[Year],[Value]),REMOVEFILTERS('Calendar')) return SUMX( VALUES('Calendar'[Year]), IF(MAX('Calendar'[DataType])="Actual",[Value],__lastActual * [Growth Rate - Dynamic]) ) 

Step 5) Feel free to leave a like & kudos 🙂

Results for all Departments: 

 

Results for Math only: 

 

PBIX FILE: https://we.tl/t-oFkef8HvHK

[ogamble]

@bolfri could you regenerate the BI link? thanks!

[yukiyuzhang]

@bolfri Hey, I found your solution is really helpful. However, the pbix file link has expired. Could you please reshare? Thanks!

[datagamble43]

Hi @bolfri 

Thank you. This works for the most part, I plan to accept it as the solution as well. One part that I am having issues with is the data type beinf the Legend. If I want to have the category as the Legend, the rates get messed up. I need to have category as the legend and this is what happens when that occurs: The 2023 year is the only year that accelerates more than the chosen growth, then they build on that. 

In addition, is there a way to take out a subject given the year? For Example, if Math were no longer to be offered from 2023 on, how would I show this where the historical data stays but the futue data is null?

[bolfri]

My mistake. The problem was with Growth Rate - Dynamic measure with almost last line. There was a missing part that tells to calculate compuntGrowRate only for DataType that is Forecast. If you will add that like it sould work perfectly.

Corrected measure:

Growth Rate - Dynamic = 
VAR __lastDateWithActual = CALCULATE(MAX('Calendar'[Year]),'Calendar'[DataType]="Actual")
VAR __thisyear = MAX('Calendar'[Year])
VAR __GrowthRatePerDepartment = 
    CALCULATE(
        AVERAGEX(
            VALUES(fact_table[Department]),
            SWITCH(fact_table[Department],
            "Business",Business[Business Value],
            "Science",Science[Science Value],
            "Technology",Technology[Technology Value],
            "Math",Math[Math Value],
            "History",History[History Value],
            1)
        ),
        ALL('Calendar')
    )
VAR __compundGrowRate = 
    CALCULATE(
        PRODUCTX(VALUES('Calendar'[Year]), 1 + __GrowthRatePerDepartment),
        'Calendar'[Year] <= __thisyear,
        'Calendar'[Year] > __lastDateWithActual,
        'Calendar'[DataType]="Forecast"
    )
RETURN __compundGrowRate

[datagamble43]

This works perfectly, thank you 🙂 

One more thing I am having trouble with, is there a way to take out a subject given the year? For Example, if Math were no longer to be offered from 2023 on, how would I show this where the historical data stays but the futue data is null?

[bolfri]

So basicly you need an table with [Category]/[Department] key with [Stop Forecasting Date] and then Value with Growth measure should return the Forecasted Value if year < that stop forecasting date? Am I correct?

[datagamble43]

@bolfri 

Hi, 

I am struggling to figure out a way where this works. I have tried adding 

IF(MAX('Department Closures'[Stop Date]) < MAX('Calendar'[Year]) around the SUMX and also tried adding it around the last If statement in the Values with Growth measure. I cannot seem to tie it together properly. Any thoughts?

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[datagamble43]

Hi @lbendlin,

Here is a table of sample data:

Year	Department	Category	Value
2020	Business	HUMAN	4300
2020	Science	STEM	4900
2020	Math	STEM	3300
2020	Technology	TECH	2500
2020	History	HUMAN	1000
2021	Business	HUMAN	4500
2021	Science	STEM	4800
2021	Math	STEM	3200
2021	Technology	TECH	2000
2021	History	HUMAN	700
2022	Business	HUMAN	5000
2022	Science	STEM	4500
2022	Math	STEM	3000
2022	Technology	TECH	3500
2022	History	HUMAN	1100
2023	Business	HUMAN	5000
2023	Science	STEM	4500
2023	Math	STEM	3000
2023	Technology	TECH	3500
2023	History	HUMAN	1100
2024	Business	HUMAN	5000
2024	Science	STEM	4500
2024	Math	STEM	3000
2024	Technology	TECH	3500
2024	History	HUMAN	1100

In addition, here is the BI file that I loaded in and created parameters with, as well as the excel file which contains the expected outcome on sheet:

BI FILE

https://1drv.ms/u/s!Aq06tAa7wTh3in7lcaaBRwjc0wJZ?e=KtdQ9I 

EXCEL FILE

https://1drv.ms/x/s!Aq06tAa7wTh3in0FAXge_jmvKTo8?e=PIGC4y 

To reword the issue -- When changing the dynamic parameter filter, the growth is based on the 2022 year and not the expected year over year growth, as seen below.