Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
10 months ago
Solved

Find if a date changes

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 colu...
Need Help
AntrikshSharma's avatar
AntrikshSharma
Community Champion
9 months ago

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.