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

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

 

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?