Forum Discussion

setis's avatar
setis
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

Forecast measure

I am working on a measure to forecast our number of cases.

The years used are 2017, 2018 and 2019 and I need to forecast the rest of 2019 and 2020.

 

What I'm basically doing for 2019  is on a day by day basis, I'm taking the cases of 2017+ 2018 divided by 2. 

The issue is that for the 2020 forecast, for dates later than today, I don't have 2019 data, so for 2020 I need the averages of 2017, 2018 and 2019 but if the date to forecast in 2020 is later than today in 2019, the calculation is 2017+ 2018 divided by 2.

I hope that this makes sense. 

I'm presenting this information in a Matrix.

 

I am trying the following but I'm not getting the expected results for 2020. 

 

# Cases Forecast = 
VAR CasesLY = 
CALCULATE ([# Cases]; DATEADD('Date'[Date]; -1;YEAR))
VAR Cases2YrsAgo = 
CALCULATE ([# Cases]; DATEADD('Date'[Date]; -2;YEAR))
VAR Cases3YrsAgo = 
CALCULATE ([# Cases]; DATEADD('Date'[Date]; -3;YEAR))
VAR _year = SELECTEDVALUE('Date'[Calendar Year])
VAR _day = SELECTEDVALUE('Date'[Date])
VAR LYToday = DATE(YEAR(NOW())-1;MONTH(NOW()); DAY(NOW()-1))
RETURN
SWITCH(
    TRUE();
    _day >= LYToday &&
    _year = "2020";
    DIVIDE(Cases2YrsAgo + Cases3YrsAgo;2;0);
    _day < LYToday &&
    _year = "2020";
    DIVIDE(CasesLY + Cases2YrsAgo + Cases3YrsAgo;3;0);
    _year = "2019";
    DIVIDE(CasesLY + Cases2YrsAgo;2;0) )

I have the feeling that this can be done in a smarter way..

 

Could somebody give me a hand here?

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi setis 

    Not sure if that is what you are looking for but try this pattern

    last three years = 
VAR _1 = 
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR( 'Calendar'[Date] )
        )
VAR _2 = 
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR(
            SAMEPERIODLASTYEAR( 'Calendar'[Date] )
        )
    )
VAR _3 = 
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR(
            SAMEPERIODLASTYEAR(
                SAMEPERIODLASTYEAR( 'Calendar'[Date] )
            )
        )
    )
VAR _all = { _1, _2, _3 }
RETURN SUMX( _all, [Value] ) / COUNTAX( _all, [Value] )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

2 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    6 years ago

    setis You need to get the number of years data that you have. In this case, you have 2017 and 2018. So try to get the DISTINCTCOUNT of year from the data which will become your denominator for future forecasting.

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi setis 

    Not sure if that is what you are looking for but try this pattern

    last three years = 
VAR _1 = 
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR( 'Calendar'[Date] )
        )
VAR _2 = 
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR(
            SAMEPERIODLASTYEAR( 'Calendar'[Date] )
        )
    )
VAR _3 = 
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR(
            SAMEPERIODLASTYEAR(
                SAMEPERIODLASTYEAR( 'Calendar'[Date] )
            )
        )
    )
VAR _all = { _1, _2, _3 }
RETURN SUMX( _all, [Value] ) / COUNTAX( _all, [Value] )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski