Forecasting Next Quarter's Data

Jenna
Jenna Member
in Beast Mode

I am trying to take the current month's visits/ Working day to forecast the next 3 months total visits per month. I would take current month's visits/ working day * next month's working days. I want the card to be interactive where there is a visits/Working day control toggle as well. I am getting stuck on the date formula based on Forecasted date to use current months data.

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    To apply the logic you mention, you'll need to come up with a formula for the avg of the current months visits. And count working days.

    CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END

    And something to calculate the average visit per working day.

    SUM(CASE WHEN YEAR(Date) = YEAR(CURRENT_DATE()) AND MONTH(Date) = MONTH(CURRENT_DATE()) THEN Visits ELSE 0 END) / SUM(CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END)

    And maybe assume a typical 22 working days month

    Average Visits per Working Day * 22

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach

    I've shared my method recently. I create formulas for CY, PY, P2Y (current year, previous year, previous 2 year)….or month such as month to date compared to previous year month to date…..using a formula where the formula compares the date in data with the current date. If you want year to date or month to date, add a line where date is less than or equal to current date.

    sum(
    
case when YEAR(date) = YEAR(CURRENT_DATE())
    
and date <= CURRENT_DATE()
    
then visits else 0 end
    
)

    If you go by month, add a line for month:

    sum(
    
case when YEAR(date) = YEAR(CURRENT_DATE())
    
and MONTH(date) = MONTH(CURRENT_DATE())
    
and date <= CURRENT_DATE()
    
then visit_amount else 0 end
    
)

    The would give you current month to date. In the first example I count visits. In the second I count amount made from those visits. The same type of formula can be applied to your forecast.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Jenna
    Jenna Member
    image.png

    This is the formula I used to test the logic for next month,

    image.png

    This is the data its returning. I want it to show March's Data for April-June as well,

    Thank you!

  • ArborRose
    ArborRose Coach

    The formula might be something like:

    sum(
    
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))
    
and MONTH(date) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))
    
then visit else 0 end
    
)

    Assuming you have entries for visits next month. Change the interval to get other future months.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Jenna
    Jenna Member

    April, May, and June, do not have visits. My end goal is to use March's Visits/ Work Day * each month's Work Day to get forecasted visits. This is a test of the logic that will be applied to more in depth formulas with interactive variables. I updated the formula to what you suggested and its returning the same values.

  • ArborRose
    ArborRose Coach
    Answer ✓

    To apply the logic you mention, you'll need to come up with a formula for the avg of the current months visits. And count working days.

    CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END

    And something to calculate the average visit per working day.

    SUM(CASE WHEN YEAR(Date) = YEAR(CURRENT_DATE()) AND MONTH(Date) = MONTH(CURRENT_DATE()) THEN Visits ELSE 0 END) / SUM(CASE WHEN WEEKDAY(Date) NOT IN (1, 7) THEN 1 ELSE 0 END)

    And maybe assume a typical 22 working days month

    Average Visits per Working Day * 22

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories