Start date from end date

[KKarlsson]

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

[Sujit_Thakur · Accepted solution]

Dear @KKarlsson ,
try this 

Measure=

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

Regards 

Sujit

[Anonymous · Accepted solution]

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

[Sujit_Thakur · Accepted solution]

Dear @KKarlsson ,
try this 

Measure=

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

Regards 

Sujit

[amitchandak]

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

[davel]

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?