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

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

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's avatar
    datagamble43
    Frequent Visitor
    3 years ago

    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?