DAX to find current project status

[telston]

I have a table of different tasks and their scheduled start and end dates.  How would I figure out which task is closest to the current date and then display that task in a seperate column in my table.  So for example, for this screenshot, I would want the "current status" to be "Design - 0.9" since that task starts 2/9/2024 and is the closest to today's date and has not ended yet.  I have already created a field that concatenates the Phase and Design Status but am stuck on how to look up the date range and return what I need.  Thanks for any help!!

[Anonymous · Accepted solution]

Hi  @telston ,

According to the error message, DAX cannot compare the value of Date type with the value of Text, you can check whether [start date] is in Text format.

You can check whether [start date] is in text format. You can change it to the correct date format in Power Query - Transform.

Or in Power BI Desktop - Column Tools

Best Regards,

Liu Yang

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

[telston]

I'm getting an error The data couldn't be loaded for the visual because the DAX couldn't compare values of type Date and values of tyler Text.  I tried filtering the table I included above and got the same thing.   I can't figure out why I am getting that error when the dates are all in Date format.

My ultimate goal is to return the most current status into another table with the project name and other project info.  

[Anonymous]

Hi  @telston ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _today=TODAY()
var _maxdate=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Start Date]<_today),[Start Date])
return
IF(
    MAX('Table'[Start Date])=_maxdate&&MAX('Table'[End Date])>_today,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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