LinkedIn
Twitter
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)
