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

[mthiru]

@dufoq3 Need help pls ! Continuing from a previous post: Solved: Re: End date based on next row (Index+1) for each ... - Microsoft Fabric Community

I am looking to calculate the End date. We need to look at the combination of (MainAccountID, BU and PV) group and if a transaction occurs for the same month, then next row start date minus 1. The dates have to be a business day. In the first record, 

Main Account ID, BU and PV (34110-100-3) has a subsequent transaction on 2018-07-09. The previous record end date is equal to previous business day 2018-07-06. 

StartDate	MainAccountID	BU	PV	Transaction	EndDate
2018-07-03	34110	100	3	5	2018-07-06
2018-07-09	34110	100	3	6	2018-07-12
2018-07-13	34110	100	3	4	2018-07-31
2018-07-03	34110	100	4	1	2018-07-06
2018-07-09	34110	100	4	2	2018-07-31

[mthiru]

@dufoq3 this is actually not what I was expecting. I may try and post another post to get help. Ideally, If I do with this less code and manually with less code intensive, using wizard in power query will be better for me.

[dufoq3]

If you want to understand the logic of previous/next row value, read this article.

[mthiru]

@dufoq3  Thank you!

I'd be no where without your help on this end date solution. If we group on AccountId, BU and PV then this should work. I have query2 with the above code and am displaying the columns in a table. I add filters to EC and PV. The first two lines should be combined, I will look into grouping that and it should work fine. 

[dufoq3]

You're welcome. Here is updated code - matching with new sample:

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        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"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdI9C4MwEAbgvxIyG7i7fJm9dBA6dRQHLYEulRIU6r+vGopQYsFA3yEhB3lyHKlr3o+Pzgde8MG/hnk7+ZtfKkxiwQiI5hoiCAB0QqAV8bxdQLcsdisIkgoclGh4U/zjgYM2fdn0yy7JOW0UJvmq7cc2TIz0qsvDOsKaJH72XYi6zdQVaTknqV/acLszUrmNmyV2h54+8z4Oa+1wr+dq7D2TkCnLmKR89c8hfpQyV7cG14E0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EC = _t, Name = _t, AccountingDate = _t, Ledger = _t, MainAccountId = _t, AcctDescription = _t, BU = _t, PV = _t, Description = _t, Amount = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"AccountingDate", type date}}),
    Ad_PrevWorkingDate = Table.AddColumn(ChangedType, "Prev. Working Date", each List.Max(List.Select(List.Dates(Date.AddDays([AccountingDate], -1), 3, #duration(-1,0,0,0)), (x)=> Date.DayOfWeek(x, Day.Monday) < 5)), type date),
    GroupedRows = Table.Group(Ad_PrevWorkingDate, {"MainAccountId", "BU", "PV"}, {{"All", each 
        [ a = fnShift(_, "Prev. Working Date", -1 , "PWD Shift"), //Prev. Working Date Shift
          b = fnShift(a, "AccountingDate", -1 , "SD Shift"), //Prev. Start Date Shift
          c = Table.AddColumn(b, "EndDate", (x)=> if Date.ToText(x[AccountingDate], [Format="yyyy-MM"]) = Date.ToText(x[SD Shift], [Format="yyyy-MM"]) then x[PWD Shift] else Date.EndOfMonth(x[AccountingDate]), type date), //Add EndDate
          d = Table.RemoveColumns(c, {"Prev. Working Date", "PWD Shift", "SD Shift"}) //Remove helper columns
        ][d], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[mthiru]

@dufoq3 The new code resolves the null values. I just noticed the end date sometimes is not as expected. In the first row, we have end date as Dec 30 2022. It should be Dec 31 2022.

EC	Name	AccountingDate	EndDate	Ledger	MainAccountId	AcctDescription	BU	PV	Description	Amount
number	text	December 31, 2022	December 30, 2022	110-0019--17-	110	text	19	17	text	-234090816
number	text	December 31, 2022	December 31, 2022	110-0019--17-	110	text	19	17	text	90816
number	text	December 31, 2022	December 31, 2022	120-0019--17-	120	text	19	17	text	82995641
number	text	January 25, 2023	January 31, 2023	120-0019--17-	120	text	19	17	text	1000000
number	text	February 27, 2023	February 28, 2023	120-0019--17-	120	text	19	17	text	4253333
number	text	March 24, 2023	March 31, 2023	120-0019--17-	120	text	19	17	text	1666667
number	text	May 31, 2023	May 31, 2023	120-0019--17-	120	text	19	17	text	5591333
number	text	June 30, 2023	June 30, 2023	120-0019--17-	120	text	19	17	text	3333333
number	text	September 8, 2023	September 30, 2023	120-0019--17-	120	text	19	17	text	3761667

[dufoq3]

It is correct from my point of view, because you've asked for: 
"if a transaction occurs for the same month, then next row start date minus 1. The dates have to be a business day"

In the first row you have date 31.12.2022 which is in the same month as second row (again 31.12.2022) and you wanted to return last business date before 2nd row date which is 30.12.2022 - Friday.

[dufoq3 · Accepted solution]

Hi @mthiru,

I've added 1 more row (comfirm if this is expected result or not).

If there is something wrong, we need to consider sort order. Let me know.

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("i45WMjIwtNA1MNc1MFbSUTI2MTQ0ANKGBiASJGKqFKuDpMgSqyIzFEWG2E0yQTUJU5EJiEXIOpAiIyRFlrqGhlgVGSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, MainAccountID = _t, BU = _t, PV = _t, Transaction = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
    Ad_PrevWorkingDate = Table.AddColumn(ChangedType, "Prev. Working Date", each List.Max(List.Select(List.Dates(Date.AddDays([StartDate], -1), 3, #duration(-1,0,0,0)), (x)=> Date.DayOfWeek(x, Day.Monday) < 5)), type date),
    GroupedRows = Table.Group(Ad_PrevWorkingDate, {"MainAccountID", "BU", "PV"}, {{"All", each 
        [ a = fnShift(_, "Prev. Working Date", -1 , "PWD Shift"), //Prev. Working Date Shift
          b = fnShift(a, "StartDate", -1 , "SD Shift"), //Prev. Start Date Shift
          c = Table.AddColumn(b, "EndDate", (x)=> if Date.ToText(x[StartDate], [Format="yyyy-MM"]) = Date.ToText(x[SD Shift], [Format="yyyy-MM"]) then x[PWD Shift] else Date.EndOfMonth(x[StartDate]), type date), //Add EndDate
          d = Table.RemoveColumns(c, {"Prev. Working Date", "PWD Shift", "SD Shift"}) //Remove helper columns
        ][d], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[dufoq3]

Hi again @mthiru, I've added 1 more row to sample data and updated a code (in prev. post). Let me know it this is what you need.

[mthiru]

Thank you @dufoq3. I have to try it with the new code. Previously I gotten the end date as expected, with one exception where in the data the PV is null. I get a null end date. Sorry, I keep forgetting to include all possible conditions in the data. We always have a MainAccountID and BU value but sometimes the PV is blank.

[dufoq3]

Try and let me know whether it works or we need to update the code (in such case, provide new sample data that covers the issue please).