summarize values by year and month

zico8

Board Regular
Joined
Jul 13, 2015
Messages
225
Hi,

I have sample data:

2016-11-20 12
2016-11-23 10
2016-12-01 45
2017-03-08 34
2017-03-09 20
2017-05-15 13

How can I summarize it on column chart in such a way as values would grouped by month and year?

November 2016: 22
December 2016: 45
March 2017: 54
May 2017: 13

?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,410
Office Version
  1. 2010
Create a Pivot chart and group as required
Just check that the dates are real dates ( right aligned in cell when no custom alignment is applied) and not text looking like dates
 

zico8

Board Regular
Joined
Jul 13, 2015
Messages
225
Thanks arthurbr, but I'd like to summarize this in Power Bi service
 

Guenther45

New Member
Joined
Nov 30, 2017
Messages
7
Hi, I'm reading in this forum some time, bit this is my first answer; so please have a little bear with me ...

is a PQ solution OK?
I added a new column with this formula:
= Date.MonthName([Datum]) & " " & Text.From(Date.Year([Datum]))
where "Datum" is the name of the column with the date (1st Col.). The name of this column is "MMMM YYYY"
Then I grouped this column by List.Sum([Number])

Hope I could help a little bit and my english is "readable". ;)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,708
Messages
5,855,257
Members
431,717
Latest member
Ibyb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top