Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |