cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Super User

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]
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

3 REPLIES 3
Frequent Visitor

Thank you very much!!

Solution Sage
``````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

Super User

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]
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

## Helpful resources

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors