undefined

[caruso1058]

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 me to build this logic:

Project	Gate	Start_Date	Finish_Date
A123	Beginning	1/1/2020	3/1/2020
A123	Partial	3/1/2020	6/1/2020
A123	Mostly	6/1/2020	10/1/2020
A123	Final	10/1/2020	10/1/2020

I am not sure what DAX formual I can write to return the Finsh Date.
All help is greatly appreciated.

[edhans · Accepted solution]

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.

[caruso1058]

Hello @edhans ,

Thank you very much for your help with this! I like your tactic, but I seem to be running into an issue with this logic, as it only returns the MAX Date within my dataset...seems to be a typo as we are not scheduling ahead that far in the future just yet, but I digress. 

Anyway, the trouble I am having is two fold, one is extracting the last task date, second is grouping this logic by each project. 

[Ashish_Mathur · Accepted solution]

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]

@Ashish_Mathur,
That works perfectly as a calculated column!  Thank you very much!

[Ashish_Mathur]

You are welcome.

[edhans · Accepted solution]

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.

[caruso1058]

Hello @edhans , 

You are right! I now see that this works as Measure. I was attempting to write this as a calcualted column, but I think the measure is going to work great!

However, I am curious now...Do you know the reason this works as a meausre but not as a calcualted column?

What logic would need to be updated in order to make this work as a calcualted column?

[edhans]

@caruso1058 it can work as a calculated column, but not as written. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query - not in calculated columns

For the scenario you are doing, you definitely want a measure.

[caruso1058]

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

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.