Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
datagamble43
Frequent Visitor

Forecasting with Parameters from Historical Data

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. 

Capture.PNG

 

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]))
 
datagamble43_0-1673962209330.png

 

 

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

1 ACCEPTED SOLUTION
bolfri
Super User
Super User

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: 

 bolfri_2-1673982119913.png

Results for Math only: 

 bolfri_1-1673982090488.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
bolfri
Super User
Super User

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: 

 bolfri_2-1673982119913.png

Results for Math only: 

 bolfri_1-1673982090488.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@bolfri could you regenerate the BI link? thanks!

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

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. 

datagamble43_0-1674057298596.png

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?

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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
Super User
Super User

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

Hi @lbendlin,

 

Here is a table of sample data:

YearDepartmentCategoryValue
2020BusinessHUMAN4300
2020ScienceSTEM4900
2020MathSTEM3300
2020TechnologyTECH2500
2020HistoryHUMAN1000
2021BusinessHUMAN4500
2021ScienceSTEM4800
2021MathSTEM3200
2021TechnologyTECH2000
2021HistoryHUMAN700
2022BusinessHUMAN5000
2022ScienceSTEM4500
2022MathSTEM3000
2022TechnologyTECH3500
2022HistoryHUMAN1100
2023BusinessHUMAN5000
2023ScienceSTEM4500
2023MathSTEM3000
2023TechnologyTECH3500
2023HistoryHUMAN1100
2024BusinessHUMAN5000
2024ScienceSTEM4500
2024MathSTEM3000
2024TechnologyTECH3500
2024HistoryHUMAN1100

 

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. 

datagamble43_0-1673961592042.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.