I need a formula to calculate avg 2 months sales on each item.

If I were basing it last years sales, I would use:

{Sold LY}/6

But I also want to include this year sales:

{Sold YTD}/todays date minus April 1, 2012 divided by 2

Anyone know how to do this? It doesn't have to be exactly 2 months avg, just something close. Also, rounded to zero decimal places.

Susan:
One way would be to break it down into sales per day and then multiply that figure by 60 days (or 60.8 using average days/month if you want to be particular).

-Number of days since April 1: Today() - Date(2012,3,31)
-Average sales per day: {Units Sold YTD} / [Number of Days]
-Sales for 2 months: [Average sales per day] * 60

The problem with this formula is that you have to edit it each April to change the calendar year. There's likely a way to work around that with some thought.

Hi Susan,

So you would like to determine the dollar amount of sales for items over approximately 2 months, based on the 'Amount sold YTD' statistic? Is this correct? If not, then please indicate what type of calculation you are looking for.

If so, then you would have to determine what percentage of your fiscal year you are into (YTD is for the fiscal year), and multiply the amount by 61/365 (the percentage of the year for about 2 months worth of days). The FISCALSTART() function can determine the start date of your current fiscal year (as determined in the Edit/Defaults screen of GridView), and the YEARFRAC(start,end) function can determine the percentage between your fiscal start and TODAY().

YEARFRAC(FISCALSTART(),TODAY())

The formula for your 2 month sales, rounded to 0 decimal places:
ROUND( (61/365) * {Amt Sold YTD} / YEARFRAC( FISCALSTART() , TODAY() ) , 0)

This is working!!

ROUND( (61/365) * {Sold YTD} / YEARFRAC( FISCALSTART() , TODAY() ) , 0)

Yeah!

Now I need to add to this what was sold last year {Sold LY}.

So the result I'm getting so far is successfully showing me 2 months average qty sold based on this year-to-date qty sold.

I want it to show me 2 months average qty sold based on this year-to-date and last year qty sold combined.

Can you help?

Actually I just realized that the formula above that is working successfully, does not work when summarize is applied. Summarize adds all the combined lines together.

Added:

Nevermind, fixed this by selecting display average instead of display total.

I would still like an answer to my question,

"So the result I'm getting so far is successfully showing me 2 months average qty sold based on this year-to-date qty sold.

I want it to show me 2 months average qty sold based on this year-to-date and last year qty sold combined.

Can you help?"

I figured out the rest so I'm good now.. thanks. I would have never figured this out without your help above.