calculate dates from once column in Power query

Hi eng_123 ,
You can also try the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldDJCsIwEAbgVwk5C+mG2rMXESuFCh5KD6MdbGhMYEyVvr0iiEungrc5zDfLX5Zy5RoripP2jZzIVEVTFQVR8qjj4FnnBqzFWlaTIZhxoPBd3Ys1wgVZNOfQDrFFy29J/wSvpo8/ur3RB7F0RtfQD10YqJBzCyCk89s/GVAvso7oPmU8tgy8ysEjWTC/MBvhCP6+N+bslkBbbY+sSDixcV6AF1dHrayqGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Leave Date" = _t, Today = _t, #"Leave Flag" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Leave Date", type date}, {"Today", type date}}),

    // Current Leave Start Date
    FilteredRowsMin = Table.SelectRows(#"Changed Type", each [Leave Date] <= [Today]),
    MinLeaveDate = Table.Group(FilteredRowsMin, {"Employee"}, {{"MinLeaveDate", each List.Min([Leave Date]), type date}}),
    MergedTablesMin = Table.NestedJoin(#"Changed Type", {"Employee"}, MinLeaveDate, {"Employee"}, "MinLeaveDateTable", JoinKind.LeftOuter),
    ExpandedTableMin = Table.ExpandTableColumn(MergedTablesMin, "MinLeaveDateTable", {"MinLeaveDate"}, {"Current Leave Start Date"}),
    #"Changed Type Min" = Table.TransformColumnTypes(ExpandedTableMin,{{"Leave Date", type date}, {"Today", type date}, {"Current Leave Start Date", type text}}),
    AddedCustomMin = Table.AddColumn(#"Changed Type Min", "Updated Leave Start Date", each if [Leave Date] > [Today] then null else [Current Leave Start Date], type nullable text),
    RemovedOriginalMin = Table.RemoveColumns(AddedCustomMin,{"Current Leave Start Date"}),
    RenamedColumnMin = Table.RenameColumns(RemovedOriginalMin,{{"Updated Leave Start Date", "Current Leave Start Date"}}),

    // Current Leave End Date
    FilteredRowsMax = Table.SelectRows(RenamedColumnMin, each [Leave Date] <= [Today]),
    MaxLeaveDate = Table.Group(FilteredRowsMax, {"Employee"}, {{"Max Leave Date", each List.Max([Leave Date]), type date}}),
    MergedTablesMax = Table.NestedJoin(RenamedColumnMin, {"Employee"}, MaxLeaveDate, {"Employee"}, "MaxLeaveDateTable", JoinKind.LeftOuter),
    ExpandedTableMax = Table.ExpandTableColumn(MergedTablesMax, "MaxLeaveDateTable", {"Max Leave Date"}, {"Current Leave End Date"}),
    #"Changed Type Max" = Table.TransformColumnTypes(ExpandedTableMax,{{"Leave Date", type date}, {"Today", type date}, {"Current Leave End Date", type text}}),
    AddedCustomMax = Table.AddColumn(#"Changed Type Max", "Updated Leave End Date", each if [Leave Date] > [Today] then null else [Current Leave End Date], type nullable text),
    RemovedOriginalMax = Table.RemoveColumns(AddedCustomMax,{"Current Leave End Date"}),
    RenamedColumnMax = Table.RenameColumns(RemovedOriginalMax,{{"Updated Leave End Date", "Current Leave End Date"}}),

    // Number of Leave Days without holidays
    FilteredRowsCount = Table.SelectRows(RenamedColumnMax, each [Leave Date] <= [Today] and [Leave Flag] <> "Weekend" and [Leave Flag] <> "Public Holiday"),
    GroupedRowsCount = Table.Group(FilteredRowsCount, {"Employee"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    MergedTableCount = Table.NestedJoin(RenamedColumnMax, {"Employee"}, GroupedRowsCount, {"Employee"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedTableCount = Table.ExpandTableColumn(MergedTableCount, "GroupedRows", {"Count"}),
    #"Changed Type Count" = Table.TransformColumnTypes(ExpandedTableCount,{{"Leave Date", type date}, {"Today", type date}, {"Count", type text}}),
    AddedCustomCount = Table.AddColumn(#"Changed Type Count", "Updated Count", each if [Leave Date] > [Today] then null else [Count], type nullable text),
    RemovedOriginalCount = Table.RemoveColumns(AddedCustomCount,{"Count"}),
    RenamedColumnCount = Table.RenameColumns(RemovedOriginalCount,{{"Updated Count", "Number of Leave Days without holidays"}}),

    // Number of Leave Days with holidays
    FilteredRowsTotal = Table.SelectRows(#"Changed Type", each [Leave Date] <= [Today]),
    GroupedRowsTotal = Table.Group(FilteredRowsTotal, {"Employee"}, {{"Total Count", each Table.RowCount(_), Int64.Type}}),
    MergedTableTotal = Table.NestedJoin(RenamedColumnCount, {"Employee"}, GroupedRowsTotal, {"Employee"}, "GroupedRowsTotal", JoinKind.LeftOuter),
    ExpandedTableTotal = Table.ExpandTableColumn(MergedTableTotal, "GroupedRowsTotal", {"Total Count"}),
    #"Changed Type Total" = Table.TransformColumnTypes(ExpandedTableTotal,{{"Leave Date", type date}, {"Today", type date}, {"Total Count", type text}}),
    AddedCustomTotal = Table.AddColumn(#"Changed Type Total", "Updated Total Count", each if [Leave Date] > [Today] then null else [Total Count], type nullable text),
    RemovedOriginalTotal = Table.RemoveColumns(AddedCustomTotal,{"Total Count"}),
    RenamedColumnTotal = Table.RenameColumns(RemovedOriginalTotal,{{"Updated Total Count", "Number of Leave Days with holidays"}})
in
    RenamedColumnTotal

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

let
    Source = YOUR_TABLE,
    holi = {"Weekend", "Public Holiday"},
    idx = Table.AddIndexColumn(Source, "idx"),
    extended_info = (tbl) => 
        ((future_start) => if future_start then [] else 
            [
                1 = List.Min(tbl[Leave Date]),
                2 = List.Max(tbl[Leave Date]),
                3 = List.Count(List.RemoveItems(tbl[Leave Flag], holi)), 
                4 = Table.RowCount(tbl)
            ]
        )(List.Min(tbl[Leave Date]) > tbl{0}[Today]),
    group = Table.Group(
        idx, 
        {"Employee", "Leave Date", "idx"}, 
        {
            {"tbl", (x) => x},
            {"info", extended_info}
        },
        GroupKind.Local, 
        (s, c) => Number.From(
            s[Employee] <> c[Employee] or 
            Duration.Days(c[Leave Date] - s[Leave Date]) <> c[idx] - s[idx]
        )
    )[[tbl], [info]],
    expand_table = Table.ExpandTableColumn(group, "tbl", {"Employee", "Leave Date", "Today", "Leave Flag"}),
    expand_record = Table.ExpandRecordColumn(
        expand_table, 
        "info", 
        {"1".."4"}, 
        {
            "Current Leave Start Date", 
            "Current Leave End Date", 
            "Number of Leave Days without holidays", 
            "Number of Leave Days with holidays"
        }
    )
in
    expand_record