Sales Predictive Analysis

[ahmedismailfourtex]

Hello,
I have 4 Items ( Product - Customer - Quantity - Date), and I need to perform Sales Prediction to know what is the predicted quantity for which customer and which period.
please let me know how to perform it step by step using Excel or Power BI.
thanks

 

[ImkeF]

In Excel there is the TREND-function, returning a linear trend on your series.

In PowerBI you can use this M-function to achieve the same result:

(YList as list, NoOfIntervalls as number) =>
let
    Source = Table.FromColumns({YList}),
    xAxis = Table.AddIndexColumn(Source, "Index", 1, 1),
    Rename1 = Table.RenameColumns(xAxis,{{"Column1", "y"}, {"Index", "x"}}),
    AvgX = List.Average(Rename1[x]),
    AvgY = List.Average(Rename1[y]),
    x = Table.AddColumn(Rename1, "xX", each [x]-List.Average(Rename1[x])),
    y = Table.AddColumn(x, "yY", each [y]-List.Average(x[y])),
    xy = Table.AddColumn(y, "xy", each [xX]*[yY]),
    xXx = Table.AddColumn(xy, "xXx", each [xX]*[xX]),
    a = List.Sum(xXx[xy])/List.Sum(xXx[xXx]),
    b = AvgY-(a*AvgX),
    ListIntervalls = {List.Max(Rename1[x])+1..List.Max(Rename1[x])+NoOfIntervalls},
    TableIntervalls = Table.FromList(ListIntervalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Rename = Table.RenameColumns(TableIntervalls,{{"Column1", "x"}}),
    Values = Table.AddColumn(Rename, "y", each [x]*a+b),
    TREND = Table.Combine({Rename1,Values})
in
    TREND

You just have to reference the column with the values of the series you have so far and the number of intervalls to be forecasted.

 

[ahmedismailfourtex]

Thanks Imke,
What about non linear trend and other predictive analysis

 

[ImkeF]

Excel has a lot of integrated functions (Growth, Logest, Solver) and Power BI has the R-integration. You might be quicker with those.

With M you can build a lot of your own non-linear regression functions as long as they are convertable into linear functions using logarithms (Number.Ln, Number.Log, Number.Log10).

Anything special you're interested in?

 

[ahmedismailfourtex]

Actually I am interested in all, I need to learn all of those