Select Max and Min date for User in table

[jwarne]

Hi Everyone,

I'm trying to work out how to build a query that will return the first and last dates of when a user was in a table. 

The data is formatted like this and I can't work out how to "select for" The values in the first row are indicitive of what I'd want to get as a result. 

Date	Username	FirstDate	LastDate
28/02/2017 0:00	accounts	2/02/2017 0:00	1/03/2017 0:00
27/02/2017 0:00	accounts
26/02/2017 0:00	accounts
25/02/2017 0:00	accounts
24/02/2017 0:00	accounts
23/02/2017 0:00	accounts
22/02/2017 0:00	accounts
21/02/2017 0:00	accounts
20/02/2017 0:00	accounts
18/02/2017 0:00	accounts
19/02/2017 0:00	accounts
17/02/2017 0:00	accounts
16/02/2017 0:00	accounts
15/02/2017 0:00	accounts
14/02/2017 0:00	accounts
13/02/2017 0:00	accounts
12/02/2017 0:00	accounts
11/02/2017 0:00	accounts
10/02/2017 0:00	accounts
9/02/2017 0:00	accounts
8/02/2017 0:00	accounts
7/02/2017 0:00	accounts
2/02/2017 0:00	accounts
3/02/2017 0:00	accounts
4/02/2017 0:00	accounts
5/02/2017 0:00	accounts
6/02/2017 0:00	accounts
6/02/2017 0:00	admin
5/02/2017 0:00	admin
4/02/2017 0:00	admin
3/02/2017 0:00	admin
2/02/2017 0:00	admin
7/02/2017 0:00	admin
8/02/2017 0:00	admin
9/02/2017 0:00	admin
10/02/2017 0:00	admin
11/02/2017 0:00	admin
12/02/2017 0:00	admin
13/02/2017 0:00	admin
14/02/2017 0:00	admin
15/02/2017 0:00	admin
16/02/2017 0:00	admin
17/02/2017 0:00	admin
19/02/2017 0:00	admin
18/02/2017 0:00	admin
20/02/2017 0:00	admin
21/02/2017 0:00	admin
22/02/2017 0:00	admin
23/02/2017 0:00	admin
24/02/2017 0:00	admin
25/02/2017 0:00	admin
26/02/2017 0:00	admin
27/02/2017 0:00	admin
28/02/2017 0:00	admin
1/03/2017 0:00	admin
3/02/2017 0:00	Test
2/02/2017 0:00	Test
7/02/2017 0:00	Test
8/02/2017 0:00	Test
9/02/2017 0:00	Test
10/02/2017 0:00	Test
11/02/2017 0:00	Test
12/02/2017 0:00	Test
13/02/2017 0:00	Test
14/02/2017 0:00	Test
15/02/2017 0:00	Test
16/02/2017 0:00	Test
17/02/2017 0:00	Test
19/02/2017 0:00	Test
18/02/2017 0:00	Test
20/02/2017 0:00	Test
21/02/2017 0:00	Test
22/02/2017 0:00	Test
23/02/2017 0:00	Test
24/02/2017 0:00	Test
25/02/2017 0:00	Test
26/02/2017 0:00	Test
27/02/2017 0:00	Test
28/02/2017 0:00	Test
1/03/2017 0:00	Test

Anyone have any advice?

Cheers, 

[rocky09]

Try this

summary = SUMMARIZE(Table1,Table1[Username],"LastDate",MAX(Table1[Date]),"FirstDate",MIN(Table1[Date]))

data needs to be format before.

accounts	12/2/2017	2/2/2017
admin	12/2/2017	1/3/2017
Test	12/2/2017	1/3/2017

[MarcelBeug]

Alternatively, you can use Group By in Power Query as illustrated in this video (dates in MM/DD/YYYY).

Not in the video: you might want to add an index column first so this can be used to get your original sort back at the end.

[Sean · Accepted solution]

Here's why I said the requirement is unclear - at first I thought @jwarne wants First and Last Date by Username

So I got the same result as @MarcelBeug

However when I compared to the result in the first row in the original post which I took to be the desired outcome

then it seems it is first and last date regardless of Username hence unclear?

BTW you can also achieve @MarcelBeug's result with DAX but instead of a new table you can add 2 Calculated Columns

[Sean]

Your requirement is unclear? Can you post desired outcome?

I was going to offer you this - but that's not what you show in the first row?

Summary Table =
SUMMARIZECOLUMNS (
    'Table'[Username],
    "FirstDate", FIRSTDATE ( 'Table'[Date] ),
    "LastDate", LASTDATE ( 'Table'[Date] )
)

EDIT: Just a clarification about the picture

I had to convert your Dates to US format so what you see as 3/1/2017 is March 1, 2017.