Forum Discussion

datagamble43's avatar
datagamble43
Frequent Visitor
3 years ago
Solved

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. 

 

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!

Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    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

10 Replies

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    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

    • datagamble43's avatar
      datagamble43
      Frequent Visitor
      3 years ago

      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's avatar
        bolfri
        Solution Sage
        3 years ago

        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

         

    • yukiyuzhang's avatar
      yukiyuzhang
      Regular Visitor
      2 years ago

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

    • datagamble43's avatar
      datagamble43
      Frequent Visitor
      3 years ago

      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.