How to pull a date from subsequent rows.

[Geeco1]

I have an employee dataset that I am pulling in through an API.  I don't have access to manipulate the data prior to pulling it into PBI.   It has 1 row for each location that  an employee has worked, along with the effective date of that row.  I need to add a end-date column and populate that date with the date from their next record's effective date, minus 1.  For example

EmpID                 Location                            Eff_Date              End_Date

1                              Factory1                             1/1/2019

1                              Factory 2                            5/1/2019

2                              Factory3                             2/1/2019

So in the above example, I would want the first row for EmpID #1 to have an End-Date of 4/30/2019.  The 2nd row for EmpID#1 could either stay null, or, ideally populate with 12/31/2999 .  The row for EmpID #2 would just have the EndDate as null or 12/31/2999.

I don't know if it is easier to do this at the Edit Queries stage or where.

[Nathaniel_C]

Hi @Geeco1 ,

This works for what you have provided us.  
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

End Date =
VAR _presentDate = 'Table'[Eff_Date]    //Get this row's date
VAR _presentID = 'Table'[EmpID]         //Get this row's EID
VAR _countRow =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[EmpID] = _presentID ) // Count rows may need this if there is more than one switch
VAR _final =
    IF (
        MAX ( 'Table'[Eff_Date] ) = _presentDate, //If max date equals present row then do the Calculate, else put in filler dated
        CALCULATE (
            MAX ( 'Table'[Eff_Date] ) - 1,
            ALLEXCEPT ( 'Table', 'Table'[EmpID] )
        ),
        DATE ( 2999, 12, 31 )
    )
RETURN
    _final

@ 

[Geeco1]

Nathaniel,

Thanks for the reply.  I am having issues when I try to apply this.  If I do it in the Report pane with the Add New Column, then it says that I have too many values being returned for the variables.  If I try to apply with the Edit Queries/Add Custom Column, then it puts a red line under the first variable name and states "Token EOF Expected"

Thanks in advance for your help.

[Nathaniel_C]

Hi @Geeco1 ,
This for a calculated column. Here is my pbix so that you can see how I did this. PBIX 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Geeco1]

Nathaniel,

Getting closer.  In looking at the results from your data, the End_Date of 4/30/2019 should be on the first line.  So basically, the first line would show an effective date of 1/1/2019 thru EndDate of 4/30/2019.  The second line would show 5/1/2019 thru 12/31/2999.

Also, I am now getting a circular reference error with the Count Rows variable.