Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbeneteli
Frequent Visitor

Get the first selling of each month

Hello,

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

 

CODENAMEDATE
11062A7/2/18 12:00 AM
11064B7/3/18 1:04 PM
11065C7/3/18 1:52 PM
11066D7/3/18 2:32 PM
11067E7/3/18 3:26 PM
11068F7/3/18 3:27 PM
11069G7/3/18 4:47 PM
11070H7/3/18 6:15 PM
11071I7/4/18 9:47 AM
11072J8/8/18 6:19 PM
11073K8/8/18 6:21 PM
11074L8/9/18 9:03 AM
11075M8/9/18 9:07 AM
11076N8/9/18 9:10 AM
11077O8/9/18 9:15 AM
11078P8/9/18 9:16 AM
11079Q6/2/18 12:33 PM
11080R6/2/18 12:54 PM
11081S6/4/18 10:10 AM
11082T6/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:

 

MONTHCODE
June11079
July11062
August 11072
1 ACCEPTED SOLUTION
Chihiro
Solution Sage
Solution Sage

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.

0.JPG

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.

1.JPG

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

 

View solution in original post

1 REPLY 1
Chihiro
Solution Sage
Solution Sage

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.

0.JPG

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.

1.JPG

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.