Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
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 |
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |