How to do Excel graphing for a rolling 12 months and YTD

I have a few years worth of sales data.  I would like to create a simple bar chart graph that displays a rolling 12 months of data and Year to Date data.  My dilemma is the data is daily.  I suppose I can create a column and do Format(date,"YYYY") to get the year, but how do I capture the rolling 12 months.  Thus using April 2018 as an example I should be displaying for the rolling 12 months each month from Apr 2017 to Apr 2018.  For the YTD line it should be from Jan 18 to Mar 31 18.

I have tried to pivot the data, but essentially I have ended up manually creating  the past 12 months of data and then the Year to date months.  I know there is an easier way.  Can anyone provide me with a link to a tutorial that may assist.  Attached is a simple raw data example.
EE-Sales.xlsx
upobDaPlayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
On the same sheet, show the desired graph output even manually.
0
Ejgil HedegaardCommented:
Check attached.
EE-Sales.xlsx
0
byundtMechanical EngineerCommented:
I put your data in a Table, and added a column for whether it occurred in the past 12 months. That column used this formula:
=[@Date]>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)

Open in new window

By using this formula in a Table column, it will automatically populate when you add new sales data.

Using that Table, I then built two PivotTable reports with dates grouped by Month and Year. The first report displayed results of YTD, and can be built by clicking Row labels...Date filters...Year to Date. The second one displayed results of the most recent 12 months, filtered using the added column in the Table.
EE-SalesQ29092440.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance AnalystCommented:
See attached.

I have converted the data to an Excel Table so that as data is added items that refer to the table will adjust automatically. This is "better" than using whole column references.

I have created a separate summary for showing the 12 months worth of data for the chart. The last date in the top row is calculated based on today's date or can be entered manually. The other 11 months are then based on that date, decreasing by one month as you go left; this is the wrong way round for normal Excel calculations as Excel normally prefers to work from left to right but is OK for a small range like this.

The chart then shows the two rows of data but have had to use a separate y value axis for each as they are so different in value; with only one axis the Commission value is not visible on the chart.

Not sure how you want to show the YTD figures.
EE-Sales.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
If you want cumulative YTD Sales instead of month by month sales, rightclick "Sum of Sales" in the PivotTable report, then choose Summarize Values By...More Options. In the resulting dialog, go to the Show Values As tab, then choose Running Total in in the "Show Values as" field, and specify Date in the "Base field".
EE-SalesQ29092440.xlsx
0
upobDaPlayaAuthor Commented:
Rob, what does the #All represent.. I have never seen that before...
0
Rob HensonFinance AnalystCommented:
That is the syntax for formulas referring to a table rather than cell references. You don't have to worry about knowing how to write that syntax, if you select a table while building the formula it will compose like that.
0
upobDaPlayaAuthor Commented:
All the replies were quite helpful.  I studied each carefully and chose Robs as his solution best fit my situation.  However, I could have chose any of the other solutions for my question, but I really liked Rob's formula in terms of clarity.  I wish though EE still had the option to award multiple solutions for rewards as I could see using some of the other solutions or combination of the solutions in my workbook.  Overall this was a great post in all that I learned from the several different approaches all that had there merits.
0
Rob HensonFinance AnalystCommented:
Thanks for the feedback. Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
pivot chart

From novice to tech pro — start learning today.