Forecast future values

[BojeErvenius]

Hi,

I have PowerPivot model with order data. I would like to forecast the number of orders being paid out in the future. In a normal Excel spreadsheet this is easy for me to do with SUMPRODUCT on a table with distribution. For example 50 % of all order are paid out same day (day 0), 25 % day 1, 5 % day 2 and so forth.

But I can’t figure out how to do it with a measure in PowerPivot? I guess I need to place my distribution as a table and in some way use SUMX.

The measure should use the number of order for “date on row” (the order date is connected to a calendar table) and multiply it with the distribution, the percent of orders that are paid out the same day (day 0), then look at the date before (yesterday) and multiply the numbers of orders with distribution for (day 1) and so forth till the end of distribution days.

Best regards
Boje Ervenius

 

[ImkeF]

Hi Boje,
yes, SUMX is the equivalent. Have a look here: Excel extras
& let me know if you need any further help on this.

Imke

 

[BojeErvenius]

Hi Boje,
yes, SUMX is the equivalent. Have a look here: Excel extras
& let me know if you need any further help on this.

Imke

Hi,

Thanks for your response. That information is explaining the use of SUMX and that example is clear to me.

But I think what I am looking for is more complex.

I am visualizing a table with distribution settings with days and percentage. For example, today we will forecast to have 25 orders paid out. Today we had 28 orders and we know from our distribution that 50% of these should be paid out today (14) + yesterday we had 20 orders and we know from distribution that 25 % of these should be paid out today (5), and so forth…

Maybe some sort of time intelligence functions in combination with SUMX?

Perhaps I am attacking the problem in a wrong way? I have searched for information about forecasting online but I can’t find anything useful.

 

[ImkeF]

Oh, I see - we're missing the link to the allocation table, sorry.

I must admit, I cannot get my head around this in Power Pivot, because it's so dead easy using Power Query:

You just join the 2 tables together, creating a cartesian product: Giving as many lines per order as there are allocation categories.
Add a new column: Date.AddDays([Date],[Day]) which is giving you the new date for the allocated amount and another new column: Amount*% giving you the allocated Portion of your amount.

Load this to your data model, connect to your DimDate from your NewDate-field - there you are

So let's hope that one of the DAX-experts in this forum will come up with a Power Pivot solution for this!