At my blog post on Calling a Stored procedure in PowerPivot asked me if we can use dates from the future in our date table to create a forecast o f some sorts.
In this blog post i am going to use future dates from my date table to determine a forecast based on values from the last 2 years. First off i have a date table in my PowerPivot with dates up until 2011. I have loaded the Contsoso database with fact table containing daysinstock values from 2007 to 2009.
What i want to do is use the values from last year and 2 years ago to determine how much better last year was compared to two years ago. To determine the values of last year and two years ago i created two simple dax functions:
daysinstock 2 years ago
daysinstock prev year
When we put this into a pivottable we see that we now have values for future years for the “daysinstock 2 years ago” measure, it will show the daysinstock 2 years ago:
So what we now can do is use these values to calculate a forecast. I have created the following DAX Function:
Daysinstock forecast =if(ISBLANK(FactInventory[daysinstock prev year]) || isblank(FactInventory[daysinstock 2 years ago]), BLANK(), FactInventory[daysinstock prev year] + (FactInventory[daysinstock prev year] / 100) * (FactInventory[daysinstock prev year] / FactInventory[daysinstock 2 years ago]))
What this function does is: Check if value of last and 2 years ago is available. If that is the case calculate the year over year growth of last year compared to two years ago, multiply this percentage with the value of last year and add this to the value of last year.
This will give you:
You could of course also use datamining to determine a real forecast as you can see in this screencast: