Find if a date changes

[gmasta1129]

Hello, 

I have a report that contains 3 columns

1. Run Date

2. Portfolio Code

3. Maturity Date

I am trying to create a formula to show if there is a difference in the maturity date column from one day to the next by portfolio code. 

Please see table below for reference. 

From Run Date 10/6/2025 to 10/7/2025, the date changed to 6/30/2028. Therefore the new column should show a "Y".  

From Run Date 10/7/2025 to 10/8/2025, the date changed again to 6/27/2027. Therefore the new column should show a "Y".  If there is no change then a "N" should show.  

Run Date	facility code	maturity date	Date Change?
10/1/2025	71803	6/27/2027	N
10/2/2025	71803	6/27/2027	N
10/3/2025	71803	6/27/2027	N
10/6/2025	71803	6/27/2027	N
10/7/2025	71803	6/30/2028	Y
10/8/2025	71803	6/27/2027	Y
10/9/2025	71803	6/27/2027	N
10/10/2025	71803	6/27/2027	N

Can someone please help with the formula? 

Thanks in advance for your help. 

[ThxAlot · Accepted solution]

[SundarRaj]

Hi @gmasta1129,
In case you'd like to do this query on Power Query. Here's a solution that you can refer to. Let me know in case there are any confusions. Thanks

Code:
let
Source = [SourceData],
Group = Table.Group(
Source,
{"facility code"},
{
{
"AllRows",
each
let
AddColumn = Table.AddIndexColumn(_, "Due Date?", 0, 1),
Transform = Table.TransformColumns(
AddColumn,
{
"Due Date?",
each try AddColumn[maturity date]{_ - 1} otherwise AddColumn[maturity date]{_}
}
),
DueColumn = Table.AddColumn(
Transform,
"Due Date",
each if [maturity date] = [#"Due Date?"] then "N" else "Y"
)
in
DueColumn
}
}
),
Combine = Table.Combine(Group[AllRows])
in
Combine

[ronrsnfld]

In Power Query, you can add an Index column to refer to the previous row.

Paste the below code into the Advanced Editor to see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjAyVdJRMje0MDAG0mb6RuYgMXOlWB2wCiOCKowJqjAjqMIcU4WxAUjMAqbCgqAZlgRVGBrgURILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Run Date" = _t, #"facility code" = _t, #"maturity date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Run Date", type date}, {"facility code", Int64.Type}, {"maturity date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Change?", each 
        if [Index] = 0 then "N" 
        else if #"Changed Type"[maturity date]{[Index]-1} = [maturity date] then "N" else "Y", type text)
in
    #"Added Custom"

[AntrikshSharma]

@gmasta1129  Try this:

let
    Source = Excel.CurrentWorkbook(){[ Name = "Table1" ]}[Content],
    ChangedType = Table.TransformColumnTypes (
        Source,
        { { "Run Date", type date }, { "facility code", Int64.Type }, { "maturity date", type date } }
    ),
    Recs = Table.ToRecords ( ChangedType ),
    Acc = List.Accumulate (
        List.Skip ( Recs ),
        { List.First ( Recs, 1 ) & [ Date Change = "N" ] },
        ( s, c ) =>
            s
                & {
                    if List.Last ( s )[#"maturity date"] <> c[#"maturity date"] then
                        c & [ Date Change = "Y" ]
                    else
                        c & [ Date Change = "N" ]
                }
    ),
    Result = Table.FromRecords (
        Acc,
        type table [ Run Date = date, facility code = Int64.Type, maturity date = date, Date Change = text ]
    )
in
    Result

Excel file attached below.

[Kedar_Pande]

Add a calculated column:

Date Change? =
VAR CurrentPortfolio = 'Table'[Portfolio Code]
VAR PreviousDate =
CALCULATE(
MAX('Table'[Maturity Date]),
FILTER(
'Table',
'Table'[Portfolio Code] = CurrentPortfolio &&
'Table'[Run Date] < EARLIER('Table'[Run Date])
)
)
RETURN
IF('Table'[Maturity Date] <> PreviousDate && NOT ISBLANK(PreviousDate), "Y", "N")

@gmasta1129

[raisurrahman]

@gmasta1129:
Thanks to @ronrsnfld  and @AntrikshSharma for their Power Query solutions—both work perfectly. For very large datasets, I’d recommend @ronrsnfld's approach; it’s faster than @AntrikshSharma ’s, because the latter transforms the object multiple times (Table → Record → Table). 

[gmasta1129]

Hello @ThxAlot ,

This worked perfectly. Thank you so much for your help. I appreciate it.