End date based on next row (Index+1) for each row

[mthiru]

Hello community, new to power query and power bi space so like some help please.

I am looking to determine the current row's end date. End date looks at the current row Start Date for example 7/3/2018 and compares with the next row's start date 7/31/2018. If the next row Startdate Index +1 is within the same month and year as the previous start date, then the end date is equal to next row's start date 7/31/2018 - 1 = 7/30/2018, otherwise give me the end of month date. 

Start Date	End Date
7/3/2018	7/30/2018
7/31/2018	7/31/2018
8/1/2018	8/13/2018
8/14/2018	8/31/2018
9/1/2018	9/30/2018
10/1/2018	10/31/2018

[mthiru]

@dufoq3 

I would refer to the Accounting Date as the start date. I grouped the view based on Accounting Date, Main Account ID, Business Unit Value and PartnerValue.  If a separate transaction occurs in the same value then I would need to look at the next row AccountingDate to get the End date for the current record. 

[dufoq3]

I'm sorry but my query works perfectly with sample data you provided. If you want to add more conditions or change something, provide new sample data and also expected result based on sample data.

[dufoq3 · Accepted solution]

Hi @mthiru, there are many ways. This one is not easy to understand, but it is realy fast.

Result

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca)
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevtValue"} 
                    else              {col & "_NextValue"} ))
        in
        c,
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31jcyMLRQitUBcwwRPAt9VI4JgmeJLGVoAOfFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}}, "en-US"),
    Ad_StartDateNextValue = fnShift(ChangedType, "Start Date", -1),
    Ad_EndDate = Table.AddColumn(Ad_StartDateNextValue, "End Date", each if Date.ToText([Start Date], [Format = "yyyy-MM"]) = Date.ToText([Start Date_NextValue], [Format = "yyyy-MM"]) then Date.AddDays([Start Date_NextValue], -1) else Date.EndOfMonth([Start Date]), type date),
    RemovedColumns = Table.RemoveColumns(Ad_EndDate,{"Start Date_NextValue"})
in
    RemovedColumns

[mthiru]

@dufoq3 yes it is not easy to understand but it worked with the sample data. I am connecting to an SQL view so there on power query with a connection to SQL table it is getting stuck. Do you have an alternative method to achieve the same result?

[dufoq3]

Could you provide screenshot of that sql table which you've loaded to power query?