Converting Table to new smaller table with only the latest dates

JamesCanale

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
1,188
Office Version
  1. 365
Platform
  1. Windows
I'm not strong with Power BI - I have a table with many records for each project, each with a date. I want to make a new table with one record for each project and the latest date for that project. Here is how I would do it in excel. Any help would be great for me - google hasn't helped me yet.

MrExcelPlayground21.xlsx
ABC
1What I have
2
3ProjectDateThing
4A12/1/202345
5A1/1/202456
6A2/1/2024451
7A3/1/2024235
8B11/1/202354
9B10/1/2023486
10B11/1/20231654
11B12/1/2023165
12B1/1/2024156
13C12/1/2023483
14C12/1/202315
15C1/1/2024456
16C2/1/2024584
17
18
19What I want
20
21ProjectDate
22A3/1/2024
23B1/1/2024
24C2/1/2024
Sheet3
Cell Formulas
RangeFormula
A22:A24A22=UNIQUE(A4:A16)
B22:B24B22=MAXIFS(B4:B16,A4:A16,A22#)
Dynamic array formulas.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
You can do it with Power Query or DAX.

DAX:

Excel Formula: 
newTable = SUMMARIZE(tbl_Projects,tbl_Projects[Project], "Date", MAX(tbl_Projects[Date]))

PQ:
Power Query: 
 grp = Table.Group(previous_Step, {"Project"}, {{"Date", each List.Max(_[Date]), type date}})

Although I think you don't need the extra table. You can use a measure to get the last date for every project.
Create a table on your report page with "Project" as first column. Create a new measure:

Excel Formula: 
M_MaxDate = MAX(tbl_Projects[Date])

Then add the measure to your table.
 
Last edited:
Upvote 0
Solution
Thanks - this is terrific. I like being on this side of things. I've got a lot to learn, but getting traction is the hardest part.
 
Upvote 0
You’re welcome! It is just practicing a lot😉
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
DAX PIVOT: Projects Closure Date Field - Number of Days Change - between differing Forecast Versions
Replies
0
Views
199
phil134
P
A
  • Question
utilisation chart for employee FTE's
Replies
0
Views
202
ajm
A
T
  • Question
Aggregation tables do not include real-time data
Replies
0
Views
165
TamirBasin
T
S
  • Solved
Pivot data returning earliest date and latest date (only) counts by a training class number
2
Replies
14
Views
668
SerenityNetworks
S
M
  • Question
Calculate the time difference
Replies
1
Views
390
Matt Allington
M

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top