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

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

ogamble's avatar
ogamble
Regular Visitor
2 years ago

bolfri could you regenerate the BI link? thanks!