Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
Need Help
Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,

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

@ 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Hi Anonymous ,
      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.