cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Geeco1
Helper I
Helper I

How to pull a date from subsequent rows.

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.

4 REPLIES 4
Nathaniel_C
Super User
Super User

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

dAte fActory.PNG

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.