Return Earliest Date

[SamWhitehouse]

Hi All, 

I have 2 tables. a Projects table showing a summary of the project and a task table showing a list of all tasks on for each project. 

Project Name	Project Manager	Project Start Date	Project Finish Date
Project 1	PM	01/01/2020	01/02/2021
Project 2	PM2	01/02/2021	01/01/2021
Project 3	PM3	01/03/20201	01/04/2011

Project Name	Task	Milestone
Project 1	Task 1	01/01/2020
Project 1	Task 2	01/02/2021
Project 2	Task 1	01/03/2020
Project  2	Task 2	01/04/2020
Project 3	Task 1	01/01/2020
Project 3	Task 1	01/01/2020

I need to be able to pull the next project milestone and name for each project.

I have tried using Lookup, however, multiple rows are returned as there may be multiple milestones on a day. 

[Anonymous · Accepted solution]

Hi

Can you try to add a calculated column on the first table with the following code:

Add a calculated column on table2
Latest Milestone =
VAR Current_Project = Table2[Project Name]
return CALCULATE(MAX(Table1[Milestone]), FILTER(Table1, Table1[Project Name] = Current_Project))

table2

Project Name

Project Manager

Project Start Date

Project Finish Date

Latest Milestone

table1

Project Name

Task

Milestone

hope this helps

It came from the following post:

https://community.powerbi.com/t5/Desktop/DAX-LOOKUPVALUE-with-MAX/td-p/696076

Thank you

Tomas