How to pull a date from subsequent rows.

Question

I have an employee dataset that I am pulling in through an API.&nbsp; I don't have access to manipulate the data prior to pulling it into&nbsp;PBI.&nbsp; &nbsp;It&nbsp;has 1 row for each location that&nbsp; an employee has worked, along with the effective date of that row.&nbsp; I need to add a end-date column and populate that date with the date from their next record's effective date, minus 1.&nbsp; For example&nbsp;EmpID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Location&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Eff_Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End_Date1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Factory1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1/1/20191&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Factory 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5/1/20192&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Factory3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2/1/2019&nbsp;So in the above example, I would want the first row for EmpID #1 to have an End-Date of 4/30/2019.&nbsp; The 2nd row for EmpID#1 could either stay null, or, ideally populate with 12/31/2999 .&nbsp; The row for EmpID #2 would just have the EndDate as null or 12/31/2999.&nbsp;I don't know if it is easier to do this at the Edit Queries stage or where.

nathaniel_c · Answer

Hi&nbsp;Anonymous&nbsp;,This works for what you have provided us.&nbsp;&nbsp;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&nbsp;&nbsp;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@&nbsp;

anonymous · Answer

Nathaniel,&nbsp;Thanks for the reply.&nbsp; I am having issues when I try to apply this.&nbsp; 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.&nbsp; 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"&nbsp;Thanks in advance for your help.

nathaniel_c · Answer

Hi&nbsp;Anonymous&nbsp;,This for a calculated column. Here is my pbix so that you can see how I did this.&nbsp;PBIX&nbsp;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

anonymous · Answer

Nathaniel,&nbsp;Getting closer.&nbsp; In looking at the results from your data, the End_Date of 4/30/2019 should be on the first line.&nbsp; So basically, the first line would show an effective date of 1/1/2019 thru EndDate of 4/30/2019.&nbsp; The second line would show 5/1/2019 thru 12/31/2999.&nbsp;Also, I am now getting a circular reference error with the Count Rows variable.

Forum Discussion

How to pull a date from subsequent rows.

4 Replies

Related content

Not all rows pulled from Dataverse

Table rows not being pulled in?

Relate the subsequent rows to previous row

Web Source not pulling

Subtract current row from subsequent row