Forum Discussion
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 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.
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.
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.
9 Replies
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.
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?
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 columnsFor the scenario you are doing, you definitely want a measure.
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.
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.
Ashish_Mathur,
That works perfectly as a calculated column! Thank you very much!
