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 differen...
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

  • 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.