Looking up the Latest Value for a Given Task Name

[nck13441]

Hello, I use Power BI to compile information from multiple projects entered into Microsoft Project (MSP). The project name is associated with a Projects table, and date-level information is associated with a Tasks table. In a normal project, each design turn-in has a single date associated with it, but if the consultant messes up their submission, they are required to resubmit. It is valuable to us to show both turn-in dates for the record in the MSP schedule.

This is fine for individual projects, but it causes problems when producing reports pulling from multiple schedules. I am trying to write a formula that will lookup the latest date for a turn-in of a given name. In the below example, this project had two turn-ins for the 60% design level. I want my report to be able to pull the later of the two turn-in dates (simplified for illustrative purposes):

Using LOOKUPVALUE is fine for everything except the projects which have multiple turn-ins of the same plan set. I spent a long time combing through other threads to resolve this problem, and I feel like LASTNONBLANK is probably the way to go, but I can't quite get it to work. Any suggestions? Thank you!

[v-shex-msft]

HI @nck13441,

You can try to use calculate function and max function to get last date based on task name and projectid.

Calculate column formula:

LastDate =
CALCULATE (
    MAX ( Task[Date] ),
    FILTER (
        ALL ( Task ),
        [ProjectID] = EARLIER ( [ProjectID] )
            && [Task Name] = EARLIER ( [Task Name] )
    )
)

Regards,

Xiaoxin Sheng

[nck13441 · Accepted solution]

This didn't quite work, but you set me on the right path! Here's what ended up working:

60% Turn In = CALCULATE ( MAX ( Tasks[FinishDate]), Tasks[taskname] = "Work on Design - 60%")

Thank you!