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
=CALCULATE(sum(FactInventory[DaysInStock]),DATEADD(DimDate[Datekey],-24,MONTH))
daysinstock prev year
=CALCULATE(sum(FactInventory[DaysInStock]),DATEADD(DimDate[Datekey],-12,MONTH))
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:
http://www.kasperonbi.com/screencast-using-datamining-with-powerpivot-in-excel
Hi,
I have couple of questions.
1) why are you dividing the previous years days in stock by 100 and later by the last 2 years
2) if i want to use data for the last 5 years to predict the next two years data, how do i do that
Hi KASPER,
Is it possible to mix forecast data with real ones ? Indeed, sometimes, we miss some data because of accouting delay, so we have a partial view. So can we build a measure to check the available data in the current period and to replace the missing ones by a forecast ?
Did you figure this out? I need to do the same thing with reported revenues.
Hi,
i have done something with employees revenues.
The goal is to make o forecast with the actual revenues and to calculate the future.