Forum Discussion
Start date from end date
Hi.
I did my best to wade through the board but could not find, or at least did not understand a way to solve my problem.
I am using a GANTT chart for project follow up and would need to create a column for start dates, which should come directly from end dates of previous project phases. "G" means Gate, so same project will have several Gates and each Gate's start date should be the end date of the previous Gate's end date. I can only get the end date from the data, so start date should be created in PBI.
This is what I mean:
| Project name & Gate | End date | Start date |
| Square G1 | 1.1.2020 | X |
| Square G2 | 15.2.2020 | 1.1.2020 |
| Square G3 | 20.3.2020 | 15.2.2020 |
| Square G4 | 15.4.2020 | 20.3.2020 |
| Square G5 | 10.6.2020 | 15.4.2020 |
| Round G1 | 15.1.2020 | X |
| Round G2 | 25.1.2020 | 15.1.2020 |
| Round G3 | 30.3.2020 | 25.1.2020 |
| Round G4 | 15.5.2020 | 30.3.2020 |
| Round G5 | 12.6.2020 | 15.5.2020 |
| Rectangle G1 | 3.3.2020 | X |
| Rectangle G2 | 12.3.2020 | 3.3.2020 |
| Rectangle G3 | 25.5.2020 | 12.3.2020 |
| Rectangle G4 | 15.6.2020 | 25.5.2020 |
| Rectangle G5 | 15.7.2020 | 15.6.2020 |
| 15.7.2020 |
Fields marked with X I would need to look up from other column named "Creation date".
Thank you in advance on all input and support. BR,
Karolus
Dear KKarlsson ,
try thisMeasure=CALCULATE(MAXX(Table1,Table1[End date]),FILTER(Table1,Table1[End date] < EARLIER(Table1[End date])
&& Table1[Project_split] = Earlier[Project_split]))
keep in mind that you split your project name from project name and gate name
for this you can go in transform data tab
dublicate that column and split it and name it as Proj_split
Please give kudos to keep me motivated
and do accept as solution if you got your answers
ask if any more doubt
RegardsSujitHI KKarlsson,
You can try to use following calculate column to extract value from your column as category and lookup the previous date.
StartDate = CALCULATE ( MAX ( T2[End date] ), FILTER ( ALLSELECTED ( T2 ), PATHITEM ( SUBSTITUTE ( [Project name & Gate], " ", "|" ), 1 ) = PATHITEM ( SUBSTITUTE ( EARLIER ( [Project name & Gate] ), " ", "|" ), 1 ) && [End date] < EARLIER ( T2[End date] ) ) )Notice: end date should be a date type field or the above formula will get the wrong result. you can use custom format on the date field to setting display format.
Regards,Xiaoxin Sheng
4 Replies
Dear KKarlsson ,
try thisMeasure=CALCULATE(MAXX(Table1,Table1[End date]),FILTER(Table1,Table1[End date] < EARLIER(Table1[End date])
&& Table1[Project_split] = Earlier[Project_split]))
keep in mind that you split your project name from project name and gate name
for this you can go in transform data tab
dublicate that column and split it and name it as Proj_split
Please give kudos to keep me motivated
and do accept as solution if you got your answers
ask if any more doubt
RegardsSujitHI KKarlsson,
You can try to use following calculate column to extract value from your column as category and lookup the previous date.
StartDate = CALCULATE ( MAX ( T2[End date] ), FILTER ( ALLSELECTED ( T2 ), PATHITEM ( SUBSTITUTE ( [Project name & Gate], " ", "|" ), 1 ) = PATHITEM ( SUBSTITUTE ( EARLIER ( [Project name & Gate] ), " ", "|" ), 1 ) && [End date] < EARLIER ( T2[End date] ) ) )Notice: end date should be a date type field or the above formula will get the wrong result. you can use custom format on the date field to setting display format.
Regards,Xiaoxin Sheng
KKarlsson , based on what I got you need to calculate the start date
start date = maxx(filter(Table, [Project name] = earlier([Project name]) && [End date] <earlier([End date])),[End date])
Hi,
Sorry to resurrect an old forum but how would you go about this if you had the start date and wanted to calcutate the end date?
