Average based on Months Passed and Months left in year

gump · 2022-11-08T17:24:29+00:00

There was an error rendering this rich post.

Hello,

Looking to take a goal I have already calculated and be able to show how much is needed on average per month until the end of the year. Additionally, calculate the average based on months passed.

An example would be a goal of 500 topics posted during the year and currently there are 250 topics posted. I have an ETL that calculates how many I need, in this example 250 topics to be posted to reach the goal. From there how can I show that with 2 months left in the year I would need 125 topics posted per month ie. Topics needed/months left in year.

Secondly, how can I show the average amounts of topics posted in the prior 10 months ie. new topics this year/months passed.

Thank you

Find more posts tagged with

Comments

GrantSmith

Are you wanting just a simple number or are you wanting to graph this over time?

If you're wanting a simple number:

Topics needed per month:

(MAX(`Goal`) - SUM(`Topics`))
/ (12 - MONTH(CURDATE()) + 1) -- +1 to include the current month

Average amount of topics prior 10 months:

CASE WHEN MONTH(CURDATE()) <> 1 THEN  -- protect against divide by 0 error for the start of the year
  SUM(`Topics`)
  /
  (MONTH(CURDATE()) - 1) -- Don't include the current month
END

gump

Hi @GrantSmith,

Thank you for the answer! I am looking to graph this over time instead of a just a simple number. Any further help with that would be appreciated!