Dax for overdue or on-time projects depending on current date

[Syndicate_Admin]

Dear Community,

I ask for your help to create a DAX with filter to determine if a project is behind schedule or on time, as you can see in the image I only have the columns of "Projects", "Stages" and "Stage Date", I need to create a DAX to determine that depending on whether Stage 4 of each project with respect to the current date is late or not, then the logic would be as follows:

For example, for project 1, since you did not find that a stage 4 exists, you should put all the rows of project 1 "no stage 4".

For project 2, as stage 4 is greater than the current date (7/23/2024) the project is on time, but I need the "On time" to be reflected throughout project 2.

For project 3, as stage 4 is less than the current date (7/23/2024) the project is overdue, but I need the "Overdue" to be reflected throughout project 3.

[Syndicate_Admin · Accepted solution]

I found this DAX, which works for me.
Overdue / On Time = IF( ISBLANK( MAXX( FILTER( 'Table', 'Table'[Projects] = EARLIER('Table'[Projects]) && 'Table'[Stage] = "Stage 4" ), 'Table'[Stage Date] ) ), "No Stage 4", IF( MAXX( FILTER( 'Table', 'Table'[Projects] = EARLIER('Table'[Projects]) && 'Table'[Stage] = "Stage 4" ), 'Table'[Stage Date] ) <= TODAY(), "Overdue", "On Time" ) )

[Anonymous]

Hi,@Syndicate_Admin 

We are very glad to know that the issue has been resolved. If you wish, consider accepting your solution as a solution that will also benefit other community members who have the same problem as you and find a solution faster.

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu