calculate dates from once column in Power query

[eng_123]

Hi All if any assiatnce on below requirements to show Current Leave Start Date, Current Leave End Date, Number of Leave Days without holidays Number of Leave Days with holidays.

Existing Column/Data:

Employee	Leave Date	Today	Leave Flag
John Smith	26/09/2024	27/09/2024	Planned
John Smith	27/09/2024	27/09/2024	Study Leave
John Smith	28/09/2024	27/09/2024	Weekend
John Smith	29/09/2024	27/09/2024	Weekend
John Smith	30/09/2024	27/09/2024	Public Holiday
John Smith	1/10/2024	27/09/2024	Carers Leave
Mary Murray	26/09/2024	27/09/2024	Mat/Paternal Leave
Mary Murray	27/09/2024	27/09/2024	Mat/Paternal Leave
John Smith	3/10/2024	27/09/2024	Training
John Smith	4/10/2024	27/09/2024	Not at work

Add new columns Current Leave Start Date, Current Leave End Date, Number of Leave Days without holidays and Number of Leave Days with holidays.

In this Requirement does not need to show (or leave blank) future leave dates not in the today's date like John Smith on 03/10/2024 and 04/10/2024.

Holidays = Weekend and Public Holiday (data exists in Leave Flag column)

Employee	Leave Date	Today	Leave Flag	Current Leave Start Date	Current Leave End Date	Number of Leave Days without holidays	Number of Leave Days with holidays
John Smith	26/09/2024	27/09/2024	Planned	26/09/2024	1/10/2024	3	6
John Smith	27/09/2024	27/09/2024	Study Leave	26/09/2024	1/10/2024	3	6
John Smith	28/09/2024	27/09/2024	Weekend	26/09/2024	1/10/2024	3	6
John Smith	29/09/2024	27/09/2024	Weekend	26/09/2024	1/10/2024	3	6
John Smith	30/09/2024	27/09/2024	Public Holiday	26/09/2024	1/10/2024	3	6
John Smith	1/10/2024	27/09/2024	Carers Leave	26/09/2024	1/10/2024	3	6
Mary Murray	26/09/2024	27/09/2024	Mat/Paternal Leave	26/09/2024	27/10/2024	2	2
Mary Murray	27/09/2024	27/09/2024	Mat/Paternal Leave	26/09/2024	27/10/2024	2	2
John Smith	3/10/2024	27/09/2024	Training
John Smith	4/10/2024	27/09/2024	Not at work

[Anonymous · Accepted solution]

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

[AlienSx · Accepted solution]

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

[Anonymous]

Hi @eng_123 ,
Assuming today is 9/30/2024, you can create the following calculated columns

Current Leave Start Date = 
VAR _leaveDate = 
CALCULATE(
    MIN('Table'[Leave Date]),
    FILTER(
        ALLEXCEPT(
            'Table',
            'Table'[Employee]
        ),
        'Table'[Leave Date] <= 'Table'[Today]
    )
)
RETURN
IF(
    'Table'[Leave Date] <= 'Table'[Today],
    _leaveDate,
    BLANK()
)

Current Leave End Date = 
VAR _leaveDate = 
CALCULATE(
    MAX('Table'[Leave Date]),
    FILTER(
        ALLEXCEPT(
            'Table',
            'Table'[Employee]
        ),
        'Table'[Leave Date] <= 'Table'[Today]
    )
)
RETURN
IF(
    'Table'[Leave Date] <= 'Table'[Today],
    _leaveDate,
    BLANK()
)

Number of Leave Days without holidays = 
VAR _Number = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT(
            'Table',
            'Table'[Employee]
        ),
        'Table'[Leave Date] <= 'Table'[Today] &&
        'Table'[Leave Flag] <> "Weekend" &&
        'Table'[Leave Flag] <> "Public Holiday"
    )
)
RETURN
IF(
    'Table'[Leave Date] <= 'Table'[Today],
    _Number,
    BLANK()
)

Number of Leave Days with holidays = 
VAR _Number = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT(
            'Table',
            'Table'[Employee]
        ),
        'Table'[Leave Date] <= 'Table'[Today]
    )
)
RETURN
IF(
    'Table'[Leave Date] <= 'Table'[Today],
    _Number,
    BLANK()
)

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

[Anonymous · Accepted solution]

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