PBI_measure_Fill in blank with a particular project number's the latest value from other columns

[MartinaRa]

I have a report which shows me the current status of projects. The project numbers are populated monthly with their monthly current status - financial and non-financial. When the project is closed in a particular month, it changes its status from approved to closed and the closing date is populated. My data table looks then like this:

(column1)Project Number (column2)Report Run Fiscal month (column3)Project Status (column4) Project Close Date

If the project is still in 'Approved' status, the 'column 4' 'Project Close date' is blank. I need to create a formula/measure to see for a particular project its latest/max Project Closing date even if I am reviewing the approved project lines.

Can you please help?

[MartinaRa · Accepted solution]

https://youtu.be/IixBzP4Htcs

[v-tangjie-msft · Accepted solution]

Hi @MartinaRa ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

max Project Closing date = MAXX(FILTER(ALL('Table'),'Table'[Project Number]=MAX('Table'[Project Number])),[Project Close Date])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[MartinaRa]

Thank you!

[MartinaRa · Accepted solution]

https://youtu.be/IixBzP4Htcs