Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
5 years ago

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

Need Help DAX

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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.

  • cottrera's avatar
    cottrera
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    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

  • cottrera's avatar
    cottrera
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

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