cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nck13441 Frequent Visitor
Frequent Visitor

Looking up the Latest Value for a Given Task Name

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):

 

Lookupvalue example.png

 

 

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
nck13441 Frequent Visitor
Frequent Visitor

Re: Looking up the Latest Value for a Given Task Name

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!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Looking up the Latest Value for a Given Task Name

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
nck13441 Frequent Visitor
Frequent Visitor

Re: Looking up the Latest Value for a Given Task Name

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!