Forecast using 2 or more financial years

[Anonymous]

Hi Everyone, 

I have a forecast calculation which works, however it looks at and uses previous year data. There are some variables with sales gaps in the previous year, thus a more reliable forecast is one that uses 2 or more years. 

The formula I have used for the forecast is :

forecast = IF(ISBLANK([Sum Sales]), CALCULATE([Sum Sales],SAMEPERIODLASTYEAR('Calendar Table'[Date])),BLANK())

- however as mentioned above this is only looking at 1 previous year 

- I cannot use the built in time intelligence as I am using a custom financial year 445 calendar, financial year begins in April and end in May.

My question is, how am I able to format the formula above in order for my forecast to use at least 2 previous years as opposed to one, or if there is any other way of doing this.

Thank you in advance, 

Pangea

[vazmor · Accepted solution]

i try

forecast =
IF (
    ISBLANK ( [Sum Sales] ),
    CALCULATE ( [Sum Sales], DATEADD('Calendar Table'[Date],-890,DAY ) ),
    BLANK ()
)

[Anonymous]

Thank you that worked perfectly 🙂