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?
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.
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).
An approriate month column
The KPI you want to forecast (in this example gross_margin)
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
add_days( -extract_days(now()), add_months( 1, now()) ) )
Day of Month - 1: calculate the current day of the month
Forecast: use the formula described above to calculate the forecast: