Forecast measure

[setis]

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?

[Mariusz · Accepted solution]

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.

[PattemManohar]

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