To Find Next Based on Financial Year (BASED ON One DATE COLUMN AND ACCOUNT CODE IN POWER QUERY)

[SharmilaBrisca]

My data contains 4 column Date,Account Code,Company and Amount.Now I want to create new column in power query editor and the new column should return the next date based on Date, Account Code and Company.....
Financial Year-Example (Starts from 01-04-2023 to End 01-03-2024)

For Example: In First Row for Account Code-89888 and Company 234 Date is 01-03-2023, now the new column(Next Date) which I create should return the same date(01-03-2023) for the same Account Code and Company 
In Second Row for Account Code-98789 and Company 876 Date is 01-06-2023, now the new column(Next Date) which I create should return the following date in the same Year(01-09-2023) for the same Account Code and Company 
If there is no Next following date for the Particular Account Code and Company then it should return year end date(01-03-20..) for the particular date

All these needs to be done in Power Query Editor

[dufoq3 · Accepted solution]

Hi @SharmilaBrisca, you posted same question again. I replied to you with solution yesterday here,

but let me paste it again:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9LDoAgDEXRvTDWpB+gr2sx7n8blhhMozhpJye9cByFeCfdhUTLVuAAYovWmA6icm636dM4DD6s9ZjWk/FkusVutY1rLRlbt2A/d1JLU4t53eLcev7FLE4yYsTj0XF0mvpj4K87/DWiLyOLVgtzXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Account Code" = _t, Company = _t, Amount = _t]),
    YourSource = Source,
    AddedIndex = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    Ad_FiscalYear = Table.AddColumn(AddedIndex, "Fiscal Year", each if Date.Month(Date.From([Date])) < 4 then Date.Year(Date.From([Date])) -1 else Date.Year(Date.From([Date])), Int64.Type),
    GroupedRows = Table.Group(Ad_FiscalYear, {"Account Code", "Company", "Fiscal Year"}, {{"All1", each _, type table}, {"All2", each _, type table}}),
    ExpandedAll1 = Table.ExpandTableColumn(GroupedRows, "All1", {"Date", "Amount", "Index"}, {"Date", "Amount", "Index"}),
    SortedRows = Table.Sort(ExpandedAll1,{{"Index", Order.Ascending}}),
    Ad_NextDate = Table.AddColumn(SortedRows, "Next Date", each 
        Date.From(
            Record.FieldOrDefault(
                Table.SelectRows(
                    [All2],
                    (r)=> r[Index] > [Index]
                ){0}?,
                "Date",
                #date([Fiscal Year] +1, 3, 1)
            )
        ), type date),
    RemovedOtherColumns = Table.SelectColumns(Ad_NextDate,{"Date", "Account Code", "Company", "Amount", "Next Date"}),
    ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"Date", type date}, {"Account Code", Int64.Type}, {"Company", Int64.Type}, {"Amount", Currency.Type}})
in
    ChangedType

[rubayatyasmin]

Hi, @SharmilaBrisca 

try this one

= Table.AddColumn(IndexedTable, "Next Date", each let
CurrentDate = [Date],
currentCode = [Account Code],
currentComp = [Company],
currentIndex = [Index],
nextRow = IndexedTable{currentIndex + 1}?
in
if Date.Month([Date]) = 3 then [Date] else if nextRow <> null and nextRow[Account Code] = currentCode and nextRow[Company] = currentComp then
nextRow[Date]
else
#date(2024, 3, 1))


this should work. 

[SharmilaBrisca]

Hi @rubayatyasmin
I am getting below image by using your solution

but in my scenario, 
For row 11 I should get 01-03-2021( because fiscal year Starts at April and ends at March)
Example(Year Starts from 01-04-2020 and End in 01-03-2021)
For row 12 I should get 01-03-2022

if we don't have following date in particular financial year then it should return the financial year end date(Date(01)-Month(03)-Year(Financial Year)

[rubayatyasmin]

Hey the solution is pretty correct. You go to the condition I wrote for the next year column. There I added 1-03-24 as else so you are getting year 24 for this.  So you need to adjust the condition a little bit. And you should be good to go