cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cottrera
Post Patron
Post Patron

DAX 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 Table

Job RefCompleted DateContractorSpend
15411401/10/2021SCP£1,695
13663102/09/2021DLO£539
18538803/10/2021DLO£1,005
17928704/08/2021SCP£994
20810804/08/2021DLO£677
20376606/07/2021DLO£452
19796407/07/2021DLO£306
20772608/06/2021SCP£1,849
14736509/05/2021SCP£1,667
13948110/04/2021SCP£1,807
15221910/04/2021SCP£1,665
13359810/04/2021SCP£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

StatusMonthDLOSCP
ActualApr£104£75,317
ActualMay£2,238£63,778
ActualJun£13,876£57,768
ActualJul£9,248£47,897
ActualAug£11,501£30,356
ActualSep£9,677£5,500
ForecastOct£7,774£46,769
ForecastNov£7,774£46,769
ForecastDec£7,774£46,769
ForecastJan£7,774£46,769
ForecastFeb£7,774£46,769
ForecastMar£7,774£46,769

 

Hopefully I have explained this ok.

 

thank you

 

Richard

3 REPLIES 3
cottrera
Post Patron
Post Patron

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

cottrera
Post Patron
Post Patron

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
MonthSpendRIchardsComments
Apr28183Actual Spend
May31523Actual Spend
Jun4096Actual Spend
Jul43831Actual Spend
Aug33873Actual Spend
Sep27275Actual Spend
Oct28130Forcast spend = Average last 6 months
Nov28130Forcast spend = Average last 6 months
Dec28130Forcast spend = Average last 6 months
Jan28130Forcast spend = Average last 6 months
Feb28130Forcast spend = Average last 6 months
Mar28130Forcast spend = Average last 6 months
   
   
MonthSpendRIchardsComments
Apr28183Actual Spend
May31523Actual Spend
Jun4096Actual Spend
Jul43831Actual Spend
Aug33873Actual Spend
Sep27275Actual Spend
Oct41231Actual Spend
Nov30002Forcast spend = Average last 7 months
Dec30002Forcast spend = Average last 7 months
Jan30002Forcast spend = Average last 7 months
Feb30002Forcast spend = Average last 7 months
Mar30002Forcast spend = Average last 7 months

 

Many thanks

Richard

amitchandak
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors