Forum Discussion
Select Max and Min date for User in table
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,
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
4 Replies
SeanCommunity Champion
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.
- rocky09
Solution Sage
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
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
