summarize values by year and month

zico8

Board Regular
Joined
Jul 13, 2015
Messages
222
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,227
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
222
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". ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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