How to populate a finish date column based on another row

[cbovikings]

I have a table where a resource can update their cost rate but it just has the StartDate (effective date). I'm trying to built out a timephased table where it has the rate per day but I can't figure out how to get a FinishDate as a column for the row.
Here is an example dataset:

Here is how I'd like the output. Note, I would substitute some date in the distant future (such as 12/31/2030) if there was no Finish Date (meaning there isn't a later row specified).

[cbovikings]

It worked the first time and then a made a minor change, changed it back, and got a circular dependency error. I tried creating from scratch and still got the circular dependency error.

[v-jingzhang · Accepted solution]

Hi @cbovikings 

First use the method you already tried to add an Index column per resource. After expanding the grouped table column, create a custom column with the following M code. Remember to replace #"Expanded Custom" with the previous step name in your query. 

let nextStartDate = Table.SelectRows(#"Expanded Custom",(x)=> x[ResourceId]=[ResourceId] and x[Index]=[Index]+1){0}[StartDate] in try Date.AddDays(nextStartDate,-1) otherwise #date(2030,12,31)

The full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUB0Ig09DAQClWBy5sBBM2RBM2hggbQYSNUA2xRBM1hyg2RRGGG21qqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ResourceId = _t, StartDate = _t, CostRate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ResourceId", Int64.Type}, {"StartDate", type date}, {"CostRate", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ResourceId"}, {{"All Data", each _, type table [ResourceId=nullable number, StartDate=nullable date, CostRate=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"StartDate", "CostRate", "Index"}, {"StartDate", "CostRate", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each let nextStartDate = Table.SelectRows(#"Expanded Custom",(x)=> x[ResourceId]=[ResourceId] and x[Index]=[Index]+1){0}[StartDate] in try Date.AddDays(nextStartDate,-1) otherwise #date(2030,12,31))
in
    #"Added Custom1"

When your data has a large number of rows, this method may take some time to get the result. Hope it helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[PijushRoy]

Hi @cbovikings 

Please try the solution mentioned in 
https://community.powerbi.com/t5/Desktop/Creating-End-Date-base-on-Next-records-Start-Date-DAX/m-p/6...

If you have any question, keep posted

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos