Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MitieFred
Helper V
Helper V

Find next date on or after Today()

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

1 ACCEPTED SOLUTION

Oh, that's easily fixed. I almost used EARLIER on purpose but just couldn't bring myself to do it... Here is the code, updated PBIX is attached. Put this measure in a table visual with Project Name.

 

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

22 REPLIES 22
Greg_Deckler
Super User
Super User

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]
       )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Best
D

@Anonymous 

 

Take a chill pill man


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Or

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"

@MitieFred - 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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Oh, that's easily fixed. I almost used EARLIER on purpose but just couldn't bring myself to do it... Here is the code, updated PBIX is attached. Put this measure in a table visual with Project Name.

 

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Awesome @Greg_Deckler , just what I was after, sad I can only give a thumbs up once

 

Regards

Fred

No worries! Glad we got there even with all the rest of it!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@MitieFred - 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Anonymous
Not applicable

@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

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 ?

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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

az38
Community Champion
Community Champion

Hi @MitieFred 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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

az38
Community Champion
Community Champion

@MitieFred 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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

 

Regards

Fred

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors