Simple month-to-date Forecasting

  • 28 February 2019
  • 6 replies
  • 2574 views

Userlevel 4

A very common piece of analysis people like to report on is projecting KPIs out for the remainder of the month. This helps them answer questions like:



  • How much revenue will I achieve by the end of the month?

  • How many customers can I expect to acquire by month end?


Method

The simplest and quickest way to create these projections is to simply take your current month performance and add it to the same value divided by the current day of month and then multiply this by the number of remaining days in the month.



Example Output

The output of this pattern will allow us to forecast the remainder of the current months performance for any KPI we are interested in (red bar indicates the forecast):


Try it Yourself - How it’s Done

Simply bring in an appropriate month column and the KPI you want to forecast (the logic below could be re-worked for weekly, quarterly on yearly projections).


Dimensions:

An approriate month column


Measures:

The KPI you want to forecast (in this example gross_margin)


Table Calcs:

The table calcs explained below could be merged into a single calculation but in order to help understand the logic they have been separated here :


Days in Month: calculate the total days in the current month


extract_days(
add_days( -extract_days(now()), add_months( 1, now()) ) )

Day of Month - 1: calculate the current day of the month


extract_days(now())-1


Forecast: use the formula described above to calculate the forecast:


 if(row()=1,
(${order_items.total_gross_margin}/${day_of_month_1})*(${days_in_month}-${day_of_month_1})
,0)


6 replies

Userlevel 4


Thanks Marcus! This have been really helpful.



Manage to implement it successfully.



29%20AM



Great explanation, Marcus!



For my use case, I needed a forecast for the month where performance was compared to the same month in the previous year. I found that I needed a forecast that was not going to be skewed heavily by early outliers (i.e. high or low spikes in performance early in the month that would have a big impact on the extrapolation). To refine the forecast, I developed a formula that would take the current year’s performance to date and then add on the previous year’s performance for the remainder of that month.



First of all, my table is set up with events / categories as the row labels, and then I have a MTD columns of data for the current year and previous year.



if(is_null(${2018_mtd}),

${2019_mtd}/extract_days(now())

*extract_days(add_days(-1,date(extract_years(add_months(1, now())),

extract_months(add_months(1, now())), 1))),

${2019_mtd}+${2018_remainder}*[forecast growth rate]))



Hope this helps anyone in a similar situation.

Userlevel 4

Hi Nick, thanks for the additional context, I actually spoke with a customer who was recently looking for just this type of analysis and with your comment it spurred me to try and create some re-usable LookML to do this. The code below could be updated to include your forecast_growth parameter too:



  measure: total_gross_margin_mtd {

type: sum value_format_name: decimal_2

sql: ${gross_margin} ;;

filters: {field:created_date value:"this month"}

}





dimension: is_last_year_mad {

type: yesno

description: column to evaluate if the date is

sql: (((CAST(order_items.created_at as TIMESTAMP) ) >= DATEADD(month,-12,current_date())

AND (CAST(order_items.created_at as TIMESTAMP) ) < ((CONVERT_TIMEZONE('Europe/London', 'UTC', DATEADD(month,1,

DATEADD(month,-12, DATE_TRUNC('month', DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'Europe/London', current_date()))) ) )))))) ;;

}



measure: total_gross_margin_last_year_mad {

type: sum value_format_name: decimal_2

sql: ${gross_margin} ;;

filters: {field:is_last_year_mad value:"Yes"}

}



measure: forecast {

value_format_name: decimal_2

type: number

sql: ${total_gross_margin_mtd} + ${total_gross_margin_last_year_mad} ;;

}

Thanks Marcus for sharing this… Can we forecast for weekdays and weekends separately and then combine

me too looking for weekday based forecast

Looking for a way to get the sales  percentage data of next few months based on past month data.

Reply