forecasting sales based on history

[MagikJukas]

Hello,

I have a table cotaning daily orders for the last three years.

I want to create a measure that predicts how many orders we are going to receive by the end of the month, based on the last two years sales.

I then want to see all this in a line chart so I can see the three years MTD sales trajectories.

Any tips?

I just created a random dataset for the sake of the exercise:

Day	Year	Month	Sales
1	2020	12	46
2	2020	12	26
3	2020	12	17
4	2020	12	25
5	2020	12	44
6	2020	12	25
7	2020	12	19
8	2020	12	50
9	2020	12	10
10	2020	12	14
11	2020	12	11
12	2020	12	19
13	2020	12	43
14	2020	12	34
15	2020	12	11
16	2020	12	35
17	2020	12	46
18	2020	12	43
19	2020	12	19
20	2020	12	37
21	2020	12	21
22	2020	12	15
23	2020	12	31
24	2020	12	25
25	2020	12	48
26	2020	12	24
27	2020	12	48
28	2020	12	17
29	2020	12	35
30	2020	12	16
1	2021	12	17
2	2021	12	17
3	2021	12	44
4	2021	12	38
5	2021	12	50
6	2021	12	29
7	2021	12	24
8	2021	12	50
9	2021	12	27
10	2021	12	44
11	2021	12	47
12	2021	12	42
13	2021	12	31
14	2021	12	21
15	2021	12	23
16	2021	12	20
17	2021	12	23
18	2021	12	43
19	2021	12	25
20	2021	12	36
21	2021	12	25
22	2021	12	18
23	2021	12	20
24	2021	12	15
25	2021	12	24
26	2021	12	25
27	2021	12	19
28	2021	12	33
29	2021	12	48
30	2021	12	15
1	2022	12	46
2	2022	12	45
3	2022	12	47
4	2022	12	29
5	2022	12	49
6	2022	12	30
7	2022	12	47
8	2022	12	49
9	2022	12	23
10	2022	12	13

[PaulOlding · Accepted solution]

Hi @MagikJukas 

First of all I used a Date table for these measures and converted your Day, Month & Year columns in Sales table to a single date field.  So, the model looks like this:

Now, here's a basic Sales amount measure and a SalesMTD measure to use in a line chart:

Sales Amount = SUM(Sales[Sales])

Sales MTD = 
VAR _MaxDateInData = MAXX(ALL(Sales), Sales[Date])
VAR _Result = IF(MAX('Date'[Date]) <= _MaxDateInData, TOTALMTD([Sales Amount], 'Date'[Date]) )
RETURN
    _Result

 I can set up a line chart like this to track MTD sales trajectories across years.  Note, the Year, Month & Day columns are from the date table:

 Lastly, the forecast measure.  There are many different rules/logic you could use to forecast sales.  This one carries on from whatever the actual sales are this month and adds the average of the same day in previous years, and returns a cumulative amount like the Sales MTD measure

Sales Forecast MTD = 
VAR _MaxDateInData = MAXX(ALL(Sales), Sales[Date])
VAR _CurrentDate = MAX('Date'[Date])
VAR _SalesMTD = TOTALMTD([Sales Amount], 'Date'[Date])
VAR _ForecastMTD = 
SUMX(
    DATESBETWEEN('Date'[Date], _MaxDateInData + 1, _CurrentDate),
    VAR _ThisDate = CALCULATE(MAX('Date'[Date]))
    RETURN
    AVERAGEX(
        FILTER(ALL('Date'), 'Date'[Month Num] = MONTH(_ThisDate) && 'Date'[Day] = DAY(_ThisDate) && 'Date'[Year] < YEAR(_ThisDate)), 
        [Sales Amount]
    )
)
VAR _Result = IF(MAX('Date'[Date]) >= _MaxDateInData, _SalesMTD + _ForecastMTD )
RETURN
    _Result

 Here's what that can look like on a line chart, along with Sales MTD.

Frustratingly it doesn't look like you can combine the line charts.  ie have Sales MTD split by Year, plus the Sales Forecast MTD all in one line chart.  Perhaps you can achieve that by layering 2 line charts on top of each other or delve into creating a custom chart with Deneb if you want that.

[MagikJukas]

Hello PO,

thanks. yesterday I worked on a solution very similar to yours. the key point was creating a new table contaning all the available days and then using formulas similar to yours.

thanks 

[FreemanZ]

hi @MagikJukas 

Power BI Desktop has zero-code forecast feature for visuals like Line Chart

check this

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

https://powerbi.microsoft.com/zh-cn/blog/introducing-new-forecasting-capabilities-in-power-view-for-...

[MagikJukas]

Thanks I will consider that.
But I would like to create something using formulas.