topic DAX to find current project status in DAX Commands and Tips

DAX to find current project status

telston — Tue, 13 Feb 2024 20:28:53 GMT

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!!

Re: DAX to find current project status

Anonymous — Wed, 14 Feb 2024 01:57:20 GMT

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

Re: DAX to find current project status

telston — Wed, 14 Feb 2024 17:32:13 GMT

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.

Re: DAX to find current project status

Anonymous — Fri, 23 Feb 2024 07:11:49 GMT

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.