Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tadawID
Frequent Visitor

check if 10 days of annual leave in a row have been taken (count consecutive dates)

Hi,

 

I have searched a lot and seen similar topics, but not solving this challenge. I have a table with all the holidays booked by 1000+ employees over one year period. The task is to check which of those emploees have taken more than 10 days in a row. So, one row represents one day of leave booked by one person (10 days booked by one person = 10 rows of data).

Putting the issue of workday and weekend/national holidays (not included in a table) aside, what could be the approach to count consecutive occurences of holidays?

I have already created auxiliary 'IsConsecutive' column by duplicating the main table, adding 0-based and 1-based indexes, then 'merging' with DAX formula:

Diff = qHolidays_AL[Start Date]-RELATED(qHolidays_AL_OFFSET[Start Date])

I can see the total number of consecutive days per Emploeey per year + consecutive number of days per period sliced with related Calandar table.

 

What I need is a measure to check if 10 days in a row has been taken by individual employee, or not (for easy filtering of both populations).

 

Struggled with EARLIER, but coudn't figure it out.

 

Appreciating any ideas!

2 ACCEPTED SOLUTIONS

Without filter context, Power Query can come to the rescue.

The code below determines the number of consecutive dates for an employee; it is not checking for any different years or any weekends.

It takes into account that the number of days may be >1 (unlike your example data).

 

I used the following data (first 3 columns as input, last column as output):

 

check if 10 days of annual leave in a row have been taken.png

 

Code with lots of comments (lines starting with //):

 

Edit: this video is a step by step walkthrough the code.

 

let
    // Next 3 lines are created with import from Excel
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\check if 10 days of annual leave in a row have been taken.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Employee ID", type text}, {"Start Date", type date}, {"Days", Int64.Type}}),
    // Sort on Employee and Start Date
    #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Employee ID", Order.Ascending}, {"Start Date", Order.Ascending}}),
    // Create individual dates for the number of Days
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "SingleDate", each List.Dates([Start Date],[Days],#duration(1,0,0,0))),
    // Expand to 1 row per single date. This will be collapsed again in the last step.
    #"Expanded SingleDate" = Table.ExpandListColumn(#"Added Custom", "SingleDate"),
    // Add 2 indices so the table can be merged with itself in such a way that values from the current row are merged with values from the previous row.
    #"Added Index" = Table.AddIndexColumn(#"Expanded SingleDate", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    // Merged step in which column is renamed to Previous
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    // Only previous Employee ID and previous ingle Date are required
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Employee ID", "SingleDate"}, {"Previous.Employee ID", "Previous.SingleDate"}),
    // Sort back on Index as the merge may have disrupted the original sort
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    // Now check if this is the second or later consecutive date for this employee
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Consecutive", each [SingleDate] = Date.AddDays([Previous.SingleDate],1) and [Employee ID] = [Previous.Employee ID]),
    // Group By on "Consecutive" with row count and All Rows. After code was created, GroupKind.Local was added to count consecutive TRUEs and FALSEs.
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Consecutive"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}, GroupKind.Local),
    // Expand the columns that are still required
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Employee ID", "Start Date", "Days", "SingleDate", "Index", "Index.1"}, {"Employee ID", "Start Date", "Days", "SingleDate", "Index", "Index.1"}),
    // Now each series of consecutive days starts with a FALSE, so we need to merge again, now with the values on the next row, to check for TRUE's after FALSEs.
    #"Merged Queries1" = Table.NestedJoin(#"Expanded AllData",{"Index.1"},#"Expanded AllData",{"Index"},"Next",JoinKind.LeftOuter),
    // We need Next.Consecutive and Next.Count
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries1", "Next", {"Consecutive", "Count"}, {"Next.Consecutive", "Next.Count"}),
    // Total number of consecutive days (including the first false)
    #"Added Custom2" = Table.AddColumn(#"Expanded Next", "Consecutive Days", each if [Consecutive] then [Count] + 1 else if [Next.Consecutive] then [Next.Count] + 1 else 1),
    // Remove columns that are no longer required.
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Employee ID", "Start Date", "Days", "Consecutive Days"}),
    // Remove duplicates so we are back to the original rows, before expansion for individual dates.
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Start Date", "Days"})
in
    #"Removed Duplicates"

 

Specializing in Power Query Formula Language (M)

View solution in original post

Hi @tadawID

 

I think if we build a caluculated column instead of a measure it might work accross filter contexts

 

Is Consecutive = 
            CALCULATE(
            COUNTROWS('Leave'),
            FILTER(ALL('Leave'),
                'Leave'[Employee ID]=EARLIER('Leave'[Employee ID])
               &&'Leave'[Start Date]=EARLIER('Leave'[Start Date])+1
                )
                )

and then 

 

Ten Days Leave = IF
            (CALCULATE(
            COUNTROWS('Leave'),
            FILTER(ALL('Leave'),
                'Leave'[Employee ID]=EARLIER('Leave'[Employee ID])
                && 'Leave'[Start Date]>=EARLIER('Leave'[Start Date])
                && 'Leave'[Start Date]<EARLIER('Leave'[Start Date])+10
                )
                )>=10,1,0)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

This measure over your Leave table would show a 1 or 0 for any individual day where it happens to be the 10th (or more) day in a row of leave.

 

10 days or more = 
IF (
	CALCULATE(
			SUM('Leave'[Is Consecutive]) ,
			ALL('Leave'[LeaveDate]),
			DATESBETWEEN(
				'Leave'[LeaveDate],
				LASTDATE('Leave'[LeaveDate])-10,
				LASTDATE('Leave'[LeaveDate])
				)
			) >= 10 , 
			1 , 
			0
			)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

that was clever! Many thanks! It took me some time to digest and understand it - lesson learned 🙂

The formula solves some of my issues, however I am still depended on my filtering context. So, when I want to check the number of 'IsConsecutive' per month, it returns the total number of consecutives occured in given month, but it could be in few separate sets (1st, 2nd, 3rd, 15th, 17th, 18th...) and still returns '1' as a result. The same would be in case whole year screen.

If I would like to check it per month (>= 18, since I need to check 10 days in a row + circa 8 weekend days in a month), it will not work correctly if someone is taking holiday across two months.

2017-03-15_12h34_47.png

 

Any chance to have a simple flag to asnwer the question if a given person took 10 days in a row (day by day) within whole year at least once?

Hi @tadawID

 

I think if we build a caluculated column instead of a measure it might work accross filter contexts

 

Is Consecutive = 
            CALCULATE(
            COUNTROWS('Leave'),
            FILTER(ALL('Leave'),
                'Leave'[Employee ID]=EARLIER('Leave'[Employee ID])
               &&'Leave'[Start Date]=EARLIER('Leave'[Start Date])+1
                )
                )

and then 

 

Ten Days Leave = IF
            (CALCULATE(
            COUNTROWS('Leave'),
            FILTER(ALL('Leave'),
                'Leave'[Employee ID]=EARLIER('Leave'[Employee ID])
                && 'Leave'[Start Date]>=EARLIER('Leave'[Start Date])
                && 'Leave'[Start Date]<EARLIER('Leave'[Start Date])+10
                )
                )>=10,1,0)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

it is exactly what I was hoping to figure out, but could not 🙂 EARLIER becomes my favorite DAX keyword from the now on! Many, many thanks for your help!

Regards

v-caliao-msft
Employee
Employee

Hi @tadawID,

 

Could you please provide us some sample data, so that we can make further analysis.

 

Regards,

Charlie Liao

Hi Charlie,

sure, I should have thought about some example too. 

To illustrate:
Employee ID Start Date Days IsConsecutive
A 1/1/2017 1 0
A 5/21/2017 1 0
B 7/1/2017 1 1
B 7/2/2017 1 1
B 7/3/2017 1 1
B 7/4/2017 1 1
B 7/5/2017 1 1
B 7/6/2017 1 1
B 7/7/2017 1 1
B 7/8/2017 1 1
B 7/9/2017 1 1
B 7/10/2017 1 1
B 7/15/2017 1 0
B 8/2/2017 1 1
B 8/3/2017 1 1
C 12/12/2017 1 0
C 12/14/2017 1 0
C 12/17/2017 1 1
C 12/18/2017 1 1

[B has 13 holidays, 12 consecutive, but only 10 consecutive in a row ---> how to isolate/group those?]

An Excel array formula to calculate it would be:
=SUM{(IF(FREQUENCY(IF(D2:D2000=1,COLUMN(D2:D2000)),IF(D2:D2000<>1,COLUMN(D2:D2000)))>

Without filter context, Power Query can come to the rescue.

The code below determines the number of consecutive dates for an employee; it is not checking for any different years or any weekends.

It takes into account that the number of days may be >1 (unlike your example data).

 

I used the following data (first 3 columns as input, last column as output):

 

check if 10 days of annual leave in a row have been taken.png

 

Code with lots of comments (lines starting with //):

 

Edit: this video is a step by step walkthrough the code.

 

let
    // Next 3 lines are created with import from Excel
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\check if 10 days of annual leave in a row have been taken.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Employee ID", type text}, {"Start Date", type date}, {"Days", Int64.Type}}),
    // Sort on Employee and Start Date
    #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Employee ID", Order.Ascending}, {"Start Date", Order.Ascending}}),
    // Create individual dates for the number of Days
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "SingleDate", each List.Dates([Start Date],[Days],#duration(1,0,0,0))),
    // Expand to 1 row per single date. This will be collapsed again in the last step.
    #"Expanded SingleDate" = Table.ExpandListColumn(#"Added Custom", "SingleDate"),
    // Add 2 indices so the table can be merged with itself in such a way that values from the current row are merged with values from the previous row.
    #"Added Index" = Table.AddIndexColumn(#"Expanded SingleDate", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    // Merged step in which column is renamed to Previous
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    // Only previous Employee ID and previous ingle Date are required
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Employee ID", "SingleDate"}, {"Previous.Employee ID", "Previous.SingleDate"}),
    // Sort back on Index as the merge may have disrupted the original sort
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    // Now check if this is the second or later consecutive date for this employee
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Consecutive", each [SingleDate] = Date.AddDays([Previous.SingleDate],1) and [Employee ID] = [Previous.Employee ID]),
    // Group By on "Consecutive" with row count and All Rows. After code was created, GroupKind.Local was added to count consecutive TRUEs and FALSEs.
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Consecutive"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}, GroupKind.Local),
    // Expand the columns that are still required
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Employee ID", "Start Date", "Days", "SingleDate", "Index", "Index.1"}, {"Employee ID", "Start Date", "Days", "SingleDate", "Index", "Index.1"}),
    // Now each series of consecutive days starts with a FALSE, so we need to merge again, now with the values on the next row, to check for TRUE's after FALSEs.
    #"Merged Queries1" = Table.NestedJoin(#"Expanded AllData",{"Index.1"},#"Expanded AllData",{"Index"},"Next",JoinKind.LeftOuter),
    // We need Next.Consecutive and Next.Count
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries1", "Next", {"Consecutive", "Count"}, {"Next.Consecutive", "Next.Count"}),
    // Total number of consecutive days (including the first false)
    #"Added Custom2" = Table.AddColumn(#"Expanded Next", "Consecutive Days", each if [Consecutive] then [Count] + 1 else if [Next.Consecutive] then [Next.Count] + 1 else 1),
    // Remove columns that are no longer required.
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Employee ID", "Start Date", "Days", "Consecutive Days"}),
    // Remove duplicates so we are back to the original rows, before expansion for individual dates.
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Start Date", "Days"})
in
    #"Removed Duplicates"

 

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug,

 

that was massive help! Big thanks! I really like the idea of merging query to itself (in previous version of my solution I have duplicated it twice to get +1 and -1 offsets). It seems to have the same performance implications (the same query size), but is deffinitely more elegant. I have also learned this GroupKin.Local from one of your other posts - it's very usefull. I have used some of the ideas + hints from Phil. Thanks for your effort!

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.