Forum Discussion

caruso1058's avatar
caruso1058
Microsoft Employee
5 years ago
Solved

undefined

I am attempting to create a Gantt Chart, however I need to supply end dates to my model. The end date of a task is the start date of the other task, so I am looking for a DAX function that will allow...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    Hi caruso1058 - see this measure:

     

    Finish Date = 
VAR varCurrentDate = MAX('Table'[Start_Date])
VAR varNextDate = 
CALCULATE(
    MIN('Table'[Start_Date]),
    FILTER(
        All('Table'),
        'Table'[Start_Date] > varCurrentDate
    )
)
RETURN
IF(
    ISBLANK(varNextDate),
    varCurrentDate,
    varNextDate
    )

     

    This will return the minium date above the current date, unless there is no next date, in which case it will repeat the current date.

     

    One note caruso1058 - you didn't specify, but I assume you want this to be by project. Note the replacement of ALL() with ALLEXCEPT()

    So you can see that the April 1, 2020 date did not impact the  A123 project.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try this calculated column formula

    =calculate(min(data[project schedule]),filter(data,data[project]=earlier(data[project])&&data[project schedule]>earlier(data[project schedule])))

    Hope this helps.

caruso1058's avatar
caruso1058
Microsoft Employee
5 years ago

edhans ,
I have watched a lot of Enterprise DNA videos in my DAX learning journey and they make the same case that, Measures are much more efficient compared to calcualted columns. 
I will look into the resources you provided, to explore this further.

 

Thanks a bunch for your help!!

edhans's avatar
edhans
Community Champion
5 years ago

Glad to be of assistance caruso1058. Calculated Columns are very "Excel Like" so people jump on them, myself included in my early Power BI days.

 

But I've learned they should be avoided except in very specific use cases.