Create a new dimension

[#1]

Is there a way to create a new dimension "End date" as following? whereby the end date will be the same project next phase start date minus 1 day??

------->

[sunny_talwar]

Try this:

Table:

LOAD * Inline [

Project, Phase, StartDate

A,1,1/6/2016

A,2,1/8/2016

A,3,1/12/2017

B,1,1/5/2015

B,2,1/8/2016

B,3,1/7/2017

];

FinalTable:

LOAD *,

  Date(If(Project = Previous(Project), Previous(StartDate) - 1)) as EndDate

Resident Table

Order By Project, StartDate desc;

DROP Table Table;

[undergrinder]

Hi Lingling,

I would create a key that contains the current Phase+1, and left join the table for itself.

The formula will be for -1 day is just simple =date(StartDate-1).

eg.:

Base:

Load

     Project

     ,Phase

     ,Project & Phase+1 as Key

     ,StartDate

Resident/From [YourTable];

left join(Base)

Load

     Project & Phase

     ,StartDate

Resident Base;

G.

[#4]

Thanks, That is what exactly I want!

[#5]

Thank you for your help