End date of one above row should be start date below row

[sarveshpandey23]

In the shared screenshot, there is two column. STARTDTM (start date) and ENDDTM (end date).

Enddate to 1st row must be start date of 2nd row.

Enddate to 2nd row must be start date of 3rd row and it goes on.

[v-yingjl · Accepted solution]

Hi @sarveshpandey23 ,

Is this your expected result?

If so, you can use this formula to create a calculated column:(Create an index column as a mark in power query firstly)

End Date =
VAR _current = 'Table'[Index]
VAR _col =
    CALCULATE (
        MAX ( 'Table'[STARTDTM] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[EMPLOYEEID] = EARLIER ( 'Table'[EMPLOYEEID] )
                && 'Table'[Index] = _current + 1
        )
    )
RETURN
    _col

Sample file is attached that hopes to help, pelase check and try it: 

 End date of one above row should be start date below row.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

Try this calculated column:

End Date = 
VAR CurrentStartDate = Dates[Start Date]
VAR NewEndDate = 
    MINX(
        FILTER(
            ALL(Dates[Start Date]),
            Dates[Start Date] > CurrentStartDate
        ),
        Dates[Start Date]
    )
RETURN
    NewEndDate

Returns this:

[edhans]

You'll need to supply data (see links below, no screenshots) and what the expected output is and why (screenshots ok for that). My method works for the date column, but you apparently need it by employee, pay code, or other stuff. None of that was explained in the original post.

[sarveshpandey23]

Hi edhans,

i tried your solution but i doesn't work. 

Here it the ouput.