topic Re: Find next date on or after Today() in DAX Commands and Tips

Find next date on or after Today()

Anonymous — Tue, 25 Feb 2020 12:28:08 GMT

A new attempt to get the NextGoLive date. Our project plans can have multiple Go-Live dates and we need to be able to show when the next one is in our weekly reports. I have cobbled together the following DAX but it is giving me the really helpful error message "The syntax for ')' is incorrect" and, to be honest, I am a little out of my depth.

Next Go Live = var NextGoLive= CALCULATE(MIN('Tasks'[TaskFinishDate]), FILTER(All('Tasks'), MIN( FILTER(Tasks,'Tasks'[TaskFinishDate]=EARLIER('Tasks'[TaskFinishDate]) && 'Tasks'[MilestoneType]="Go Live")&& 'Tasks'[TaskFinishDate].[Date]>=TODAY() && 'Tasks'[TaskIsExternal]=FALSE()&& 'Tasks'[TaskIsSummary]=FALSE()&& 'Tasks'[TaskIsMilestone]=TRUE()&& IF('Tasks'[MilestoneType]="Go Live", 'Tasks'[NextGoLive],0))))

I have also tried the following, it gives no errors, but instead of a date for anything in the future or past, it only outputs zero but this might be down to a DAX nested AND constraint.

NextGoLive = if( AND( AND( AND(Tasks[TaskIsMilestone]=TRUE(),Tasks[TaskIsExternal]=FALSE()), AND(Tasks[MilestoneType]="GoLive",Tasks[TaskIsSummary]=FALSE())), Tasks[TaskFinishDate].[Date]>=Today()), Tasks[TaskFinishDate].[Date],0)

Thanks and regards

Fred

Re: Find next date on or after Today()

az38 — Tue, 25 Feb 2020 12:55:54 GMT

Hi Anonymous

it is difficult to give a correct answer without a data example but in first statement try to remove string

var NextGoLive=

second statement rewrite as

NextGoLive = if( Tasks[TaskIsMilestone]=TRUE() && Tasks[TaskIsExternal]=FALSE() && Tasks[MilestoneType]="GoLive" && Tasks[TaskIsSummary]=FALSE() && Tasks[TaskFinishDate]>=Today(), Tasks[TaskFinishDate],0)

pay attention to remove .[Date] part

Re: Find next date on or after Today()

Greg_Deckler — Tue, 25 Feb 2020 12:59:50 GMT

Your first formula looks OK, but I believe you want to be using MINX instead of MIN. The error message you are getting indicates that you have unmatched parens ( ). Try something like below:

Next Go Live = var NextGoLive= MINX( FILTER( All('Tasks'), 'Tasks'[TaskFinishDate]=EARLIER('Tasks'[TaskFinishDate]) && 'Tasks'[MilestoneType]="Go Live")&& 'Tasks'[TaskFinishDate].[Date]>=TODAY() && 'Tasks'[TaskIsExternal]=FALSE()&& 'Tasks'[TaskIsSummary]=FALSE()&& 'Tasks'[TaskIsMilestone]=TRUE()&& 'Tasks'[MilestoneType]="Go Live" ), 'Tasks'[TaskFinishDate] )

Re: Find next date on or after Today()

Anonymous — Wed, 26 Feb 2020 10:56:18 GMT

Hi Greg, Greg_Deckler , thanks for the pointers . . . . .

I needed to modify the code slightly, not really changing the overall effect, but the "EARLIER" statement gives me an error, "A single value for column 'TaskFinishDate' cannot be determined". It's a date field on each task entry, so I'm not sure what the problem is.

Next Go Live = MINX( FILTER( All('Tasks'), 'Tasks'[TaskFinishDate]=EARLIER('Tasks'[TaskFinishDate]) && 'Tasks'[MilestoneType]="Go Live" && 'Tasks'[TaskFinishDate].[Date]>=TODAY() && NOT('Tasks'[TaskIsExternal]) && NOT('Tasks'[TaskIsSummary]) && 'Tasks'[TaskIsMilestone] ), 'Tasks'[TaskFinishDate] )

I tried using the TaskIndex field but got the same error. The data is all single entry on each Task, so TaskIsExternal, TaskIsSummary and TaskIsMilestone are all either True or False, MilestoneType is one of Level 1, Level 2 or Go Live and TaskFinishDate is just a date field, there cannot be multiple finish dates on a task, you would need to have multiple tasks . . . . . would that cause a problem if they had the same TaskName ?

Re: Find next date on or after Today()

Greg_Deckler — Wed, 26 Feb 2020 12:58:28 GMT

Hmm, I think I need some sample data to test this with and see what the problem is. Any chance you can provide sample data in text? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Find next date on or after Today()

Anonymous — Wed, 26 Feb 2020 16:25:07 GMT

Greg_Deckler I've put a spreadsheet of data on a shared drive here

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 11:47:01 GMT

Hi Greg_Deckler

For completeness,

Tasks table Projects table

ProjectId *:1 ProjectId Single

The end result, I'd hope for is that the NextGoLive field would populate on the Projects table (i.e. single entry) rather than on the Tasks entries, which would be one entry per task.

Thanks in advance

Fred

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 13:11:59 GMT

Hi az38 Aleksei, I modified the code as follows

NextGoLive = if( AND( AND( AND(Tasks[TaskIsMilestone],NOT(Tasks[TaskIsExternal])), AND(Tasks[MilestoneType]="GoLive",NOT(Tasks[TaskIsSummary]))), AND(Tasks[TaskFinishDate]>=Today(),NOT(Tasks[TaskIsProjectSummary]))), Tasks[TaskFinishDate],31/12/1999)

but ended up with every task showing the NextGoLive as 30/12/1899, totally ignoring the default date I expected to see. The data I'm working from can be found here

Regards

Fred

Re: Find next date on or after Today()

az38 — Mon, 02 Mar 2020 13:35:07 GMT

Anonymous

why did you do that? didnt you try the statements above?

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 13:42:51 GMT

Hi az38 Aleksei, yes, I did try but they gave me a date of 30/12/1899 on all tasks

Regards

Fred

Re: Find next date on or after Today()

az38 — Mon, 02 Mar 2020 14:05:11 GMT

Anonymous

its because of space in "Go Live"

try a column

NextGoLive = if( Tasks[TaskIsMilestone]=TRUE() && Tasks[TaskIsExternal]=FALSE() && Tasks[MilestoneType]="Go Live" && Tasks[TaskIsSummary]=FALSE() && Tasks[TaskFinishDate]>=Today(), Tasks[TaskFinishDate],date(1999,12,31))

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 14:47:30 GMT

Hi @az38 Aleksei, ah, so obvious, thankyou for that.

How do I now get the minimum go live date that is either today of after today ? That's why I tried creating a column with MINX(Filter in the Projects table, however, at present, that gives me blanks, no dates/values at all.

Next Go Live = MINX( FILTER( All('Tasks'), 'Tasks'[TaskFinishDate]=MIN('Tasks'[TaskFinishDate]) && 'Tasks'[MilestoneType]="Go Live" && 'Tasks'[TaskFinishDate].[Date]>=TODAY() && NOT('Tasks'[TaskIsExternal]) && NOT('Tasks'[TaskIsSummary]) && 'Tasks'[TaskIsMilestone] ), 'Tasks'[TaskFinishDate] )

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 15:36:09 GMT

Greg_Deckler, could you please stop using EARLIER? Please use variables instead.

Best
D

Re: Find next date on or after Today()

Greg_Deckler — Mon, 02 Mar 2020 15:58:41 GMT

Anonymous

Take a chill pill man

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 16:11:36 GMT

Greg_Deckler,

EARLIER is not only obsolete, it's not recommended. I know you have some die-hard habits, it's understandable to a point, but you're not making it easier for others to understand your code. Since VAR's have been introduced there's no need for EARLIER anymore.

Best
D

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 16:15:15 GMT

Sorry Greg_Deckler Anonymous az38 did I miss something 😁

The current versions of code in "use" are as follows, both are columns

Next Go Live = MINX( FILTER( All('Tasks'), 'Tasks'[TaskIsMilestone] && 'Tasks'[TaskFinishDate]=MIN('Tasks'[TaskFinishDate]) && 'Tasks'[MilestoneType]="Go Live" && 'Tasks'[TaskFinishDate]>=TODAY() && NOT('Tasks'[TaskIsExternal]) && NOT('Tasks'[TaskIsSummary]) ), 'Tasks'[TaskFinishDate] )

This gives blank results, no date at all.

NextGoLive = if( Tasks[TaskIsMilestone] && NOT(Tasks[TaskIsExternal]) && Tasks[MilestoneType]="Go Live" && NOT(Tasks[TaskIsSummary]) && Tasks[TaskFinishDate]>=Today(), Tasks[TaskFinishDate],date(1999,12,31))

which gives dates greater than or equal to Today or 31/12/1999, on all tasks, but does not give a single, lowest date value and I can't see any way to pick that "next date after today"

Re: Find next date on or after Today()

Greg_Deckler — Mon, 02 Mar 2020 17:08:31 GMT

Anonymous - I just downloaded the Excel spreadsheet so let me have a look and see. Will need some time.

Anonymous - I respect your opinion. But the forums are not for chastising people or spouting opinions. Use a blog for that. And I have been through 10 pages of search results looking for EARLIER is obsolete or not recommended or deprecated and nothing. Nothing in the official Microsoft documents about it. I know Marco doesn't recommend it on his page but that's all I've ever seen. But then again he doesn't like SUMMARIZE even though you have to use it sometimes because there is no perfect alternative. Anyway, perhaps you should write a blog about it and you can chastise people there and keep it off of the forums. I provide solutions for people that work. I don't claim that those solutions are always best practice or the most computationally efficient or make any other claims. Generally they are not because they are quick things that I bang out in 15 minutes or less. And they are free. So I don't have time to do all the fancy formatting and make sure I put in the table names everywhere and all that because I am not being paid to produce the best DAX code of all time. When I get paid I try to make sure I follow all the best practices, etc. But nobody is perfect. People are more than welcome to take my solutions and modify the code to put it into best practices and have all the proper formatting. So, in closing, chill out, this is a nice place for nice people to do nice things for one another.

Re: Find next date on or after Today()

Anonymous — Mon, 02 Mar 2020 17:38:54 GMT

So you have to be paid to do things properly? That's a very interesting stance. But I don't second it. I respect your opinion as well but I also care about the readers.

By the way, SUMMARIZE is OK to use when you don't make any calculations inside it. Otherwise, you'll be creating headaches and bugs before you know it. And they'll be almost impossible to find, especially for those less experienced.

All I asked you for was not to use EARLIER and use variables instead - is this really that hard that you have to get paid to follow this practice? On top of that, you are shooting at me with a cannon. And I'm the one to take a pill... 🙂

But you know? After all, who cares. I don't because I know DAX. And those who don't will take your word for it and will be happy. Right?

Have a well-paid evening 🙂

Best
D

Re: Find next date on or after Today()

Greg_Deckler — Mon, 02 Mar 2020 18:43:43 GMT

Anonymous - First apologies for this thread getting off track.

Second, I am not 100% clear on exactly what you are going for exactly, but I believe that this code returns the next Go Live date perhaps?

Next Go Live = MINX( FILTER( All('Tasks'), 'Tasks'[TaskIsMilestone] = TRUE() && //'Tasks'[TaskFinishDate]=MIN('Tasks'[TaskFinishDate]) && 'Tasks'[MilestoneType]="Go Live" && 'Tasks'[TaskFinishDate]>=TODAY() && 'Tasks'[TaskIsExternal] = FALSE() && 'Tasks'[TaskIsSummary] = FALSE() ), 'Tasks'[TaskFinishDate] )

PBIX is attached.

Re: Find next date on or after Today()

Anonymous — Tue, 03 Mar 2020 13:00:55 GMT

Hi Greg_Deckler , not a problem with off-piste thread, it comes with the territory

Re the question, thanks, that's definitely on the right track. That code gave me the next Go Live across all projects, what I was hoping for was the next go live for each project as some projects have only 1 go live but others have staged go lives and we want to pick up the next one for each in either type of project. I tried the code in both the Projects and Tasks tables (in MS Project) and it gives the same, single, go live date.

Thanks for your efforts/patience on this, it's much appreciated.

Fred