Creating dynamic date bins based on the date of true/false toggle

[DK_analyst]

Good Afternoon!

I've been asked to run analytics on my company's attendance data. The dataset contains one row per date per employee for all of 2023, and then a true/false flag that indicates if they have declared they will be available to work that day. See the example data:

Date	Name	Available
1/1/2023	Barney	TRUE
1/2/2023	Barney	TRUE
1/3/2023	Barney	FALSE
1/4/2023	Barney	FALSE
1/5/2023	Barney	FALSE
1/6/2023	Barney	TRUE
1/7/2023	Barney	TRUE
1/8/2023	Barney	FALSE
1/9/2023	Barney	TRUE
1/10/2023	Barney	TRUE
1/11/2023	Barney	FALSE
1/12/2023	Barney	FALSE
1/13/2023	Barney	FALSE
1/14/2023	Barney	FALSE
1/1/2023	Fred	FALSE
1/2/2023	Fred	FALSE
1/3/2023	Fred	FALSE
1/4/2023	Fred	TRUE
1/5/2023	Fred	TRUE
1/6/2023	Fred	TRUE
1/7/2023	Fred	TRUE
1/8/2023	Fred	TRUE
1/9/2023	Fred	FALSE
1/10/2023	Fred	FALSE
1/11/2023	Fred	FALSE
1/12/2023	Fred	TRUE
1/13/2023	Fred	TRUE
1/14/2023	Fred	TRUE

For the purpose of scheduling, I've been asked to take that data and create a quick reference for when each employee becomes available, and for how many days each time until their next segment of unavailability. In other words, take the above table and turn it into something that looks like this:

Name	Availability Date	Available days
Barney	1/1/2023	2
Barney	1/6/2023	2
Barney	1/9/2023	2
Fred	1/4/2023	5
Fred	1/12/2023	3

My thought was to figure out a way to group each block of availability by employee, and then create measures to find the first date in each group and then how many days that group includes. Though for the life of me I can't figure out how.

For simplicity's sake, I am not correcting availability days for weekends and company holidays.

Thanks in advance!

[DK_analyst]

Thank you very much!!

[Ahmedx]

let
    Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\dynamic.xlsx"), null, true),
    tbl_Sheet = Source{[Item="tbl",Kind="Sheet"]}[Data],
    PromoteHeaders = Table.PromoteHeaders(tbl_Sheet, [PromoteAllScalars=true]),
    typx = Table.TransformColumnTypes(PromoteHeaders,{{"Date", type date}, {"Name", type text}, {"Available", type logical}}),
    PreviousRow = Table.FromColumns (Table.ToColumns(typx)&{{false}&List.RemoveLastN(typx[Available],1)}),
    Check = Table.AddColumn(PreviousRow, "Custom", each if [Column3] =true and[Column4] =false then [Column1] else null),
    GroupRows = Table.Group(Check, {"Column2"}, {{"Day", each _, type table [Column1=nullable date, Column2=nullable text, Column3=nullable logical, Column4=logical, Custom=nullable date]}}),
    f = (x)=> Table.FillDown(x,{"Custom"}),
    Custom1 = Table.TransformColumns(GroupRows,{"Day",f}),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Day"}),
    #"Expanded Day" = Table.ExpandTableColumn(#"Removed Other Columns", "Day", {"Column1", "Column2", "Column3", "Column4", "Custom"}, {"Column1", "Column2", "Column3", "Column4", "Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Day",{"Column4"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Day", each if [Column3] = true then 1 else 0),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Day", type number}, {"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", "Name"}, {"Column1", "Date"}, {"Custom", "FDate"}, {"Column3", "Available"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([FDate] <> null))
in
    #"Filtered Rows"

Sample PBIX file attached
https://dropmefiles.com/LrZVH

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _indexcolumn =
    ADDCOLUMNS ( Data, "@index", IF ( Data[Available] = TRUE (), 0, 1 ) )
VAR _groupcolumn =
    ADDCOLUMNS (
        _indexcolumn,
        "@group",
            SUMX (
                FILTER (
                    _indexcolumn,
                    Data[Name] = EARLIER ( Data[Name] )
                        && Data[Date] <= EARLIER ( Data[Date] )
                ),
                [@index]
            )
    )
VAR _filterworkdays =
    FILTER ( _groupcolumn, Data[Available] = TRUE () )
RETURN
    SUMMARIZE (
        GROUPBY (
            _filterworkdays,
            Data[Name],
            [@group],
            "@availabilitydate", MINX ( CURRENTGROUP (), Data[Date] ),
            "@availabledays", SUMX ( CURRENTGROUP (), 1 )
        ),
        Data[Name],
        [@availabilitydate],
        [@availabledays]
    )