How do I calculate a date based off the previous date for the same criteria

Edited for clarity.

I have a dataset of different clients who have different documents due at different intervals.

I want to use PowerQuery to calculate the next due date of the document based on the previous date of the same document for the same client.

In the original table, called "Raw_Data_Table". It has three columns. One of them will be the client names, the second will be the different documents, and the third will be when the document was completed.

Name	Record Type	Done Date
Client 1	Employment	01/01/24
Client 1	Employment	06/12/23
Client 1	Employment	12/14/22
Client 1	Functional	11/12/24
Client 1	Functional	05/12/24
Client 1	Nicotine	12/14/22
Client 2	Employment	06/14/22
Client 2	Employment	12/14/23
Client 2	Functional	09/16/24
Client 2	Functional	03/14/24
Client 2	Functional	09/10/23
Client 2	Nicotine	07/29/24
Client 2	Nicotine	07/23/23

Raw_Data_Table

I want to be able to determine when a document's due date was based on the previous date of the same document. For example, the Due Date for the Employment Assessment for Client 1 that was done on 01/01/24, would have been 12/01/23. If I compare the two dates, I will know that it was completed in time, so this document was actually done late.

In a very crude way, I can achieve that by using this formula in D2:

=IF(AND(A2=A3,B2=B3,B2="Employment Assessment"),EOMONTH(C3+180,-1)+1,
IF(AND(A2=A3,B2=B3,B2="Functional Assessment"),EOMONTH(C3+180,-1)+1,
IF(AND(A2=A3,B2=B3,B2="Nicotine Assessment"),EOMONTH(C3+365,-1)+1,
"")))

This isn't ideal because it is prone for errors if the table gets sorted differently. I want to do it through a Query that just creates the column "Due Date" by finding the previous date a document type was created for a client and calculating the due date based on that previous date.

This is how I would like the results to appear.

Documents_with_Due_Dates

Solution

You can do this using the Table.Group function.
Group by Name and Record Type, then create a custom aggregation.

I ensured proper order by sorting the date column within each subtable. This step could be omitted if the dates will always be in descending order.

If your original data is in some kind of sorted order that you would like to retain for the final output, add an Index column before the Table.Group function and use that to restore the original order.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Record Type", type text}, {"Done Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Record Type"}, {
        {"Due Date", (t)=>
            let 
                sort = Table.Sort(t,{"Done Date", Order.Descending}),
                #"Added Index"=Table.AddIndexColumn(sort,"Index",0,1,Int64.Type),
                #"Added Due Date" = Table.AddColumn(#"Added Index","Due Date",
                        each try Date.AddMonths(
                                    Date.StartOfMonth(#"Added Index"{[Index]+1}[Done Date]),
                                    if [Record Type]="Nicotine" then 12 else 6) 
                        otherwise null)
            in 
                #"Added Due Date", type table[Name=text, Record Type=text, Done Date=date, Due Date=date]
            }}),
            
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Name", "Record Type"}),
    #"Expanded Due Date" = Table.ExpandTableColumn(#"Removed Columns", "Due Date", {"Name", "Record Type", "Done Date", "Due Date"})
in
    #"Expanded Due Date"