Forum Discussion
Helper ICompare yearly data from one date column
Hi guys,
I have a sheet with actually 5 columns
1. Office
2. Year of sale (ex:2014 in a text format)
3. Month of sale (ex: January in a text format)
4. Number of sale
5. Date (date format)
Knowing that I have a date column, I want to use that column to display the number of sale and compare the numbers month by month. I want to have one chart with 3 columns for each month of the year.
Do I need to change the format of my excel sheet to have a column for each year? is there a better way?
Thanks
Eric
4 Replies
- MattAllington
Community Champion
You can use the data as is, but best practice is to load your data without the year and month columns and then add a calendar table. I have an article about calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/
when you say you want 3 columns, what do you mean?
- elatreille
Hi Matt,
This is what I did and forgot to mention.
But still don't see how to use the date column and display the data in one chart with 3 column for the 3 years period.
- MattAllington
The problem is that your date column only contains the date. You need a year column to do what you want to do. Once you have a year column, you can put the year on the ledgened and filter for the years you want in the chart.
Take a look at my article on calendar tables in my knowledge base here http://exceleratorbi.com.au/power-pivot-calendar-tables/
v-caliao-msftMicrosoft Employee
Hi Elatreille,
You can use clustered column chart to achieve thie requirement. Drag Month of sale to Axis, Year of sale to legend and Number of sale to Value.
Regards,
Charlie Liao
