Forum Discussion
cottrera
Post Prodigy
Post ProdigyDAX create forecast based on historical data
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 Tab...
cottreraPost Prodigy
Post ProdigyHi 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