Get the first selling of each month

[rbeneteli]

Hello,

I have a table with a lot of data as example:

CODE	NAME	DATE
11062	A	7/2/18 12:00 AM
11064	B	7/3/18 1:04 PM
11065	C	7/3/18 1:52 PM
11066	D	7/3/18 2:32 PM
11067	E	7/3/18 3:26 PM
11068	F	7/3/18 3:27 PM
11069	G	7/3/18 4:47 PM
11070	H	7/3/18 6:15 PM
11071	I	7/4/18 9:47 AM
11072	J	8/8/18 6:19 PM
11073	K	8/8/18 6:21 PM
11074	L	8/9/18 9:03 AM
11075	M	8/9/18 9:07 AM
11076	N	8/9/18 9:10 AM
11077	O	8/9/18 9:15 AM
11078	P	8/9/18 9:16 AM
11079	Q	6/2/18 12:33 PM
11080	R	6/2/18 12:54 PM
11081	S	6/4/18 10:10 AM
11082	T	6/4/18 10:24 AM

I'd like to create a new table with  the code of the first selling of each month like this:

MONTH	CODE
June	11079
July	11062
August	11072

[Chihiro · Accepted solution]

There are many ways to do it...

1. Using Matrix Visual

Make sure [CODE] is set to Text type column.

Add [DATE] as row and set to Month hierarchy.

Add [CODE] as values and set to First.

Edit: Forgot to remove Total. You can do so at visual option menu. 

2. In query editor.

Again make sure [CODE] is Text data type.

Reference the query (right click in query pane and choose Reference).

Add Custom Column [Month] and set it to Text type.

=Date.MonthName([DATE])

Group by Month, and MIN of [Date] column.

Merge Original into reference query using Left Outer. Expand CODE.

Remove Min of Date column.

Note: You can add sort on Min of Date (asc) before removing it.