Finding Most Recent Date of Each Projects

[Hasan_iba]

Hello Power BI experts. Hope all of you are doing well. I am facing an issue which is summarized as follows: 

I have a project management dataset which includes project name, history date and project health (which shows the status of the project: Green, Red or Yellow). I need to find the most recent date of each of the project and then extract the status of the project on that most recent date. The following image should clarify: 

For example, for Apparel ERP project, the most recent date is 5/9/2007. For other projects, most recent projects may be other dates such as 5/3/2017....5/2/2017 and so on. I want to find the most recent dates for each of the projects and based on that date, i need to find the project health status. I tried to use last date function but showing error. 

I need to highlight the most recent project status with colored indicators like the following snap: 

I have figured out the historic health status which shows the status of a project on a specific date selected. 

Here is a summary of what i need to find (As my post has got a bit lengthy. So, giving a sum up)

1. I am looking to find the most recent date for each of the projects

2. Based on the most recent date, I want to find the status of the project 

3. I want to indicate the status of project for most recent date with colored indicator. 

Here is the pbix file i am working with: https://drive.google.com/file/d/0B0XwYvmbIN12REpUVXRGbG1ickU/view?usp=sharing

A solution will be highly appreciated. Thanks a lot

Hasan

[v-yuezhe-msft · Accepted solution]

Hi @Hasan_iba,

Create the MaxDate measure and “Is Latest“ column in your table using the following DAX.

MaxDate = CALCULATE(MAX(HistoryCompare[HistoryDate]), ALL(HistoryCompare))
Is Latest = if(HistoryCompare[HistoryDate] = [MaxDate], "Latest", "")

Then create a table visual as shown in the screenshot below, drag “Is Latest“ column to visual level filters and filter it to “Latest”.

Additionally, regarding to the color indicator issue, please review smoupre’s reply in your another thread.

Thanks,
Lydia Zhang

[oscarflorez99]

What if the latest date for every product varies from one another? Because I have realized that i Hassan's example, the most recent date for each "Name" is 5/9/2017. What if product "A" most recent date is 5/9/2017 and for product "B" is 17/12/2018? I have tried the DAX you provided Lydia and it does not work when the most recent dates are different for each product.

Many thanks!

[Anonymous]

I'd suggest making calculated column called "Current Health" and using LASTNONBLANK.  You could use this in a formula to only consider records that share the "Name" column.

[Hasan_iba]

Hi @Anonymous can you provide some details on how to use the lastnonblank formula? What should be the inputs? Thanks