DAX create forecast based on historical data

[cottrera]

Hi 

I have a facts table with 4 columns job ref, completed date, Contactor and Spend.  The completed dates start Apr 2021 and will eventually end 30th Mar 2022 (our financial year) 

Facts Table

Job Ref	Completed Date	Contractor	Spend
154114	01/10/2021	SCP	£1,695
136631	02/09/2021	DLO	£539
185388	03/10/2021	DLO	£1,005
179287	04/08/2021	SCP	£994
208108	04/08/2021	DLO	£677
203766	06/07/2021	DLO	£452
197964	07/07/2021	DLO	£306
207726	08/06/2021	SCP	£1,849
147365	09/05/2021	SCP	£1,667
139481	10/04/2021	SCP	£1,807
152219	10/04/2021	SCP	£1,665
133598	10/04/2021	SCP	£448

I would like to create a forcast DAX measure for all incomplete or partially completed months. Although today is 11th October I would not include October into the forcast as October is not yet fininshed. I would also like the DAX forcast measure to be based on all previously completed months of our financial year. So in this example if would be Apr, May, Jun, Jul, Aug and Sept = 6 months of historical data. Then on 1st November we will have a completed October month so the forcast measure would then need to look at 7 months of data and so on.

My final Matrix will look something like this

Status	Month	DLO	SCP
Actual	Apr	£104	£75,317
Actual	May	£2,238	£63,778
Actual	Jun	£13,876	£57,768
Actual	Jul	£9,248	£47,897
Actual	Aug	£11,501	£30,356
Actual	Sep	£9,677	£5,500
Forecast	Oct	£7,774	£46,769
Forecast	Nov	£7,774	£46,769
Forecast	Dec	£7,774	£46,769
Forecast	Jan	£7,774	£46,769
Forecast	Feb	£7,774	£46,769
Forecast	Mar	£7,774	£46,769

Hopefully I have explained this ok.

thank you

Richard

[cottrera]

Hi 
Just wondering if there an update to my response.  best regards Richard

[cottrera]

Hi Amitchandak thank you for your quick response. 

The DAX funtion provided required a small amount of editing to sort of work However it provide the total amount. Where as I need the average for the 6 months.  Also I require it to be dynamic , so that once October 2021 month has past. The -6 changes to -7 and once November 2021 has past the -7 changes to -8.

Code provided

new DLO = if( max('Date'[Date]) <=eomonth(today(),-1), [DLO] CALCULATE([DLO],DATESINPERIOD('Date'[Date ],eomonth(today(),-1),-6,MONTH)) )

My amended code

Last 6 month spend = if( max('Date'[Date]) <=eomonth(today(),-1), CALCULATE([Spend],DATESINPERIOD('Date'[Date],eomonth(today(),-1),-6,MONTH)) )

To get the Avg I did the following 

Avg Spend last 6 months =

VAR TotalspednLast6Months = if( max('Date'[Date]) <=eomonth(today(),-1), CALCULATE([£Spend], DATESINPERIOD('Date'[Date],eomonth(today(),-1),-6,MONTH)) )

VAR Months = 6

VAR Results =DIVIDE(TotalspednLast6Months,Months)

RETURN

Results

Also I need to show this measure next to the actual spend table / graph so that it shows as a forcast after the actuals.

Here is an example of how I plan to display the results

Month	Spend	RIchardsComments
Apr	28183	Actual Spend
May	31523	Actual Spend
Jun	4096	Actual Spend
Jul	43831	Actual Spend
Aug	33873	Actual Spend
Sep	27275	Actual Spend
Oct	28130	Forcast spend = Average last 6 months
Nov	28130	Forcast spend = Average last 6 months
Dec	28130	Forcast spend = Average last 6 months
Jan	28130	Forcast spend = Average last 6 months
Feb	28130	Forcast spend = Average last 6 months
Mar	28130	Forcast spend = Average last 6 months
Month	Spend	RIchardsComments
Apr	28183	Actual Spend
May	31523	Actual Spend
Jun	4096	Actual Spend
Jul	43831	Actual Spend
Aug	33873	Actual Spend
Sep	27275	Actual Spend
Oct	41231	Actual Spend
Nov	30002	Forcast spend = Average last 7 months
Dec	30002	Forcast spend = Average last 7 months
Jan	30002	Forcast spend = Average last 7 months
Feb	30002	Forcast spend = Average last 7 months
Mar	30002	Forcast spend = Average last 7 months

Many thanks

Richard

[amitchandak]

@cottrera , Assuming month is coming from a date table

new DLO = if( max('Date'[Date]) <=eomonth(today(),-1), [DLO] CALCULATE([DLO],DATESINPERIOD('Date'[Date ],eomonth(today(),-1),-6,MONTH)) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.