# Forecasting Next Quarter's Data

Options
Member

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.

• Coach
Options

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

** Did this solve your problem? Accept it as a solution! **

• Coach
Options

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.

** Did this solve your problem? Accept it as a solution! **

• Member
Options

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

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

Thank you!

• Coach
Options

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.

** Did this solve your problem? Accept it as a solution! **

• Member
Options

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.

• Coach
Options

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