DAX | Count consecutive days of absence

[NicolaiW]

Hello Guys,

Here is a challenge for you. I hope you can help me out!

I have this dataset with employees registered working days. I want to create a visualization consisting of the employee, and a measure of the number of periods(>1day) of absence. So if an employee has one day of absence it should not be calculated in the measure, but only if they have periods of abcense that are more than one day. I want to count how many of these periods each employee has had. see picuture below for more explanation.

Keep in mind, that there will be several employees checking in the same day. So there will be more rows with the same date. 

I am looking forward to see how you solve this. Good luck! 🙂

/Nicolai

[OwenAuger · Accepted solution]

Hi @NicolaiW 

Sure thing 🙂

To handle that particular issue (and anything involving more complexity with dates) I recommend creating a 'Date' table that includes columns

• "Is Workday": Boolean indicating whether a given date is a workday (Mon-Fri)
• "Workday Index": An index which increments by one on each workday, but is blank on weekends.

In my updated PBIX this looks like this:

Then to handle weekends by effectively ignoring them, rewrite the measure as follows:

# Absence Periods Ignoring Weekends = 
VAR Threshold =
    2
-- Combinations of Date/Employee for absence days
VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Data, 'Date'[Workday Index], Data[Employee ID] ),
        Data[Day of Absence] = "Yes",
        'Date'[Is Workday] -- equivalent to 'Date'[Is Workday] = TRUE
    )
-- Add @PeriodID which uniqely identifies each contiguous absence period per employee
VAR AddPeriodID =
	ADDCOLUMNS (
		AbsenceDays,
		"@PeriodID",
		VAR DateRank =
			RANK ( DENSE, AbsenceDays, ORDERBY ( 'Date'[Workday Index], ASC ), DEFAULT, PARTITIONBY ( Data[Employee ID] ) )
		VAR Result =
			DateRank - 'Date'[Workday Index]
		RETURN
			Result
	)
-- @DaysPerPeriod is the # days of absence in each period per employee
VAR DaysPerPeriod =
	GROUPBY (
		AddPeriodID,
		[@PeriodID],
		Data[Employee ID],
		"@DaysPerPeriod", SUMX ( CURRENTGROUP(), 1 )
	)
-- Filter to absence periods >= Threshold
VAR FilterThreshold =
	FILTER (
		DaysPerPeriod,
		[@DaysPerPeriod] >= Threshold
	)
-- Count the resulting absence periods    
VAR Result =
    COUNTROWS ( FilterThreshold )
RETURN
    Result

I added Employee 4 who was absent for every Mon-Fri in January. Combined with the existing data, the results look like below.

You will note that:

1. Employee 4 indeed has 1 absence ignoring weekends.
2. Employee 2 now has only 1 absence. This is because the new measure ignores the absence on Sat 7-Jan, so that absence is only 1 workday long and not counted.
3. Employee 1 now has no absences. This is because the original absence was Sat 28-Jan to Mon 30-Jan, and is now only 1 workday long and not counted.

Question: Do weekend absences still need to be taken into account in any way, for the purpose of determining the length of an absence period, or can they be safely ignored in practice?

We may need to rewrite the measure if so.

Regards

[OwenAuger · Accepted solution]

@NicolaiW 

Hi again Nicolai,

No worries 🙂

For Average Days per Absence, I would suggest a measure like this (updated PBIX attached with additional made-up data).

Average Days per Absence = 
VAR Threshold =
    2
-- Combinations of Date/Employee for absence days
VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Data, 'Date'[Workday Index], Data[Employee ID] ),
        Data[Day of Absence] = "Yes",
        'Date'[Is Workday] -- equivalent to 'Date'[Is Workday] = TRUE
    )
-- Add @PeriodID which uniqely identifies each contiguous absence period per employee
VAR AddPeriodID =
	ADDCOLUMNS (
		AbsenceDays,
		"@PeriodID",
		VAR DateRank =
			RANK ( DENSE, AbsenceDays, ORDERBY ( 'Date'[Workday Index], ASC ), DEFAULT, PARTITIONBY ( Data[Employee ID] ) )
		VAR Result =
			DateRank - 'Date'[Workday Index]
		RETURN
			Result
	)
-- @DaysPerPeriod is the # days of absence in each period per employee
VAR DaysPerPeriod =
	GROUPBY (
		AddPeriodID,
		[@PeriodID],
		Data[Employee ID],
		"@DaysPerPeriod", SUMX ( CURRENTGROUP(), 1 )
	)
-- Filter to absence periods >= Threshold
VAR FilterThreshold =
	FILTER (
		DaysPerPeriod,
		[@DaysPerPeriod] >= Threshold
	)
-- Count the resulting absence periods    
VAR Result =
    AVERAGEX (
        FilterThreshold,
        [@DaysPerPeriod]
    )
RETURN
    Result

Hope that works for you 🙂

Regards

[NicolaiW]

Thanks Mate,

I'm out of Questions now 😀

/Nicolai

[OwenAuger]

Hi @NicolaiW 

I particularly like this method, which labels each contiguous period of absence. Each period is labelled by taking the difference between the date itself and the "rank" of the date among dates of absence.

Here is the measure, and PBIX attached.

Note that (blank) rather than zero is returned if there are no periods of absence. This could be adjusted if needed.

# Absence Periods = 
VAR Threshold =
    2
-- Combinations of Date/Employee for absence days
VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[Date], Data[Employee ID] ),
        Data[Day of Absence] = "Yes"
    )
-- Add @PeriodID which uniqely identifies each contiguous absence period per employee
VAR AddPeriodID =
	ADDCOLUMNS (
		AbsenceDays,
		"@PeriodID",
		VAR DateRank =
			RANK ( Dense, AbsenceDays, ORDERBY ( Data[Date], ASC ), Default, PARTITIONBY ( Data[Employee ID] ) )
		VAR Result =
			DateRank - Data[Date]
		RETURN
			Result
	)
-- @DaysPerPeriod is the # days of absence in each period per employee
VAR DaysPerPeriod =
	GROUPBY (
		AddPeriodID,
		[@PeriodID],
		Data[Employee ID],
		"@DaysPerPeriod", SUMX ( CURRENTGROUP(), 1 )
	)
-- Filter to absence periods >= Threshold
VAR FilterThreshold =
	FILTER (
		DaysPerPeriod,
		[@DaysPerPeriod] >= Threshold
	)
-- Count the resulting absence periods    
VAR Result =
    COUNTROWS ( FilterThreshold )
RETURN
    Result

Regards

[NicolaiW]

Hi @OwenAuger 
Awesome coding!
Howerver the DAX code you made, doesn't work with weekends. So if an employee has a period of absence of a month, it will be registered as 4 periods because of the 3 weekends. Could you perhaps come up with a solution for this? 🙂
Thanks
/Nicolai

[OwenAuger · Accepted solution]

Hi @NicolaiW 

Sure thing 🙂

To handle that particular issue (and anything involving more complexity with dates) I recommend creating a 'Date' table that includes columns

• "Is Workday": Boolean indicating whether a given date is a workday (Mon-Fri)
• "Workday Index": An index which increments by one on each workday, but is blank on weekends.

In my updated PBIX this looks like this:

Then to handle weekends by effectively ignoring them, rewrite the measure as follows:

# Absence Periods Ignoring Weekends = 
VAR Threshold =
    2
-- Combinations of Date/Employee for absence days
VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Data, 'Date'[Workday Index], Data[Employee ID] ),
        Data[Day of Absence] = "Yes",
        'Date'[Is Workday] -- equivalent to 'Date'[Is Workday] = TRUE
    )
-- Add @PeriodID which uniqely identifies each contiguous absence period per employee
VAR AddPeriodID =
	ADDCOLUMNS (
		AbsenceDays,
		"@PeriodID",
		VAR DateRank =
			RANK ( DENSE, AbsenceDays, ORDERBY ( 'Date'[Workday Index], ASC ), DEFAULT, PARTITIONBY ( Data[Employee ID] ) )
		VAR Result =
			DateRank - 'Date'[Workday Index]
		RETURN
			Result
	)
-- @DaysPerPeriod is the # days of absence in each period per employee
VAR DaysPerPeriod =
	GROUPBY (
		AddPeriodID,
		[@PeriodID],
		Data[Employee ID],
		"@DaysPerPeriod", SUMX ( CURRENTGROUP(), 1 )
	)
-- Filter to absence periods >= Threshold
VAR FilterThreshold =
	FILTER (
		DaysPerPeriod,
		[@DaysPerPeriod] >= Threshold
	)
-- Count the resulting absence periods    
VAR Result =
    COUNTROWS ( FilterThreshold )
RETURN
    Result

I added Employee 4 who was absent for every Mon-Fri in January. Combined with the existing data, the results look like below.

You will note that:

1. Employee 4 indeed has 1 absence ignoring weekends.
2. Employee 2 now has only 1 absence. This is because the new measure ignores the absence on Sat 7-Jan, so that absence is only 1 workday long and not counted.
3. Employee 1 now has no absences. This is because the original absence was Sat 28-Jan to Mon 30-Jan, and is now only 1 workday long and not counted.

Question: Do weekend absences still need to be taken into account in any way, for the purpose of determining the length of an absence period, or can they be safely ignored in practice?

We may need to rewrite the measure if so.

Regards

[ElliotCartlidge]

Hello, stumbled across this for my similar situation. 
This works but the issue i have is that i want to show Absences by Month. The above would count an abense which spans over a month barrier as 2 different absences. 
How would i get it to only count 1 absence, ideally shown in the month the absence started.

[OwenAuger]

Hi there @ElliotCartlidge 🙂

Here's one suggestion (updated PBIX attached).

Create # Absence Periods by Start Date by modifying # Absence Periods.

The main updates are:

• Expand the range of dates to determine whether periods of absence begain during the filtered date range, and whether they meet the threshold (after the filtered date range (MinDateAdj and MaxDateAdj).
• In the DaysPerPeriod variable, a "@PeriodStart" column is added containing the first date of the absence period. In the EnforceDateFilter variable, this is then filtered to absence periods starting in the filtered date range.

# Absence Periods by Start Date = 
VAR Threshold =
    2
-- Period will be expanded by looking back one day and forward Threshold - 1 days
-- to determine whether qualifying absence periods begain in filtered date range
VAR MinDateAdj =
    MIN ( 'Date'[Date] ) - 1
VAR MaxDateAdj =
    MAX ( 'Date'[Date] ) + Threshold - 1

-- Combinations of Date/Employee for absence days
VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Data, 'Date'[Date], Data[Employee ID] ),
        Data[Day of Absence] = "Yes",
        DATESBETWEEN ( 'Date'[Date], MinDateAdj, MaxDateAdj )
    )
-- Add @PeriodID which uniqely identifies each contiguous absence period per employee
VAR AddPeriodID =
	ADDCOLUMNS (
		AbsenceDays,
		"@PeriodID",
		VAR DateRank =
			RANK ( DENSE, AbsenceDays, ORDERBY ( 'Date'[Date], ASC ), DEFAULT, PARTITIONBY ( Data[Employee ID] ) )
		VAR Result =
			DateRank - 'Date'[Date]
		RETURN
			Result
	)
-- @DaysPerPeriod is the # days of absence in each period per employee
VAR DaysPerPeriod =
	GROUPBY (
		AddPeriodID,
		[@PeriodID],
		Data[Employee ID],
		"@DaysPerPeriod", SUMX ( CURRENTGROUP(), 1 ),
        "@PeriodStart", MINX ( CURRENTGROUP (), 'Date'[Date] )
	)
-- Use a join to include only  absences beginning intside filtered date range
VAR EnforceDateFilter =
	NATURALINNERJOIN ( DaysPerPeriod, SELECTCOLUMNS ( VALUES ( 'Date'[Date] ), "@PeriodStart", 'Date'[Date] + 0  ) )

-- Filter to absence periods >= Threshold that begin during period
VAR FilterThreshold =
	FILTER (
		EnforceDateFilter,
        [@DaysPerPeriod] >= Threshold
    )
    
-- Count the resulting absence periods    
VAR Result =
    COUNTROWS ( FilterThreshold )
RETURN
    Result

I made up some extra data for testing. For now I have left Threshold = 2 but this can be changed.

Sample visual:

This measure is likely ripe for optimisation.

It would also be possible to process the data before loading so that it is loaded as three columns:

Employee ID | Absence Start Date | Absence Duration

This would greatly simplify the DAX.

I will revisit when I have a chance 🙂

Is this the sort of thing you were looking for?

Regards

[ElliotCartlidge]

Hi Owen,

This works great, the only issue i see is that its correctly not counting weekends as absences, however if an Absence goes over a weekend into the next week, this will be counted as 2 seperate occurances, instead of 1. We'd ideally like to count this as 1 occurance. 

[OwenAuger]

Just clarifying the issue with an example:

If an absence goes from Thursday to the following Tuesday it is currently counted as two absences but should be counted as one?

[ElliotCartlidge]

Correct, weekends shouldn't be able to seperate an absence.

[OwenAuger]

Great thanks 🙂
I think the "easiest" method is to rewrite the measure using Workday Index rather than Date.

I haven't fully tested this but this is what I'm thinking:

# Absence Periods by Start Date (ignore weekends) = 
VAR Threshold =
    2
-- Period will be expanded by looking back one day and forward Threshold - 1 days
-- to determine whether qualifying absence periods begain in filtered date range
VAR MinDateAdj =
    MIN ( 'Date'[Workday Index] ) - 1
VAR MaxDateAdj =
    MAX ( 'Date'[Workday Index] ) + Threshold - 1

-- Combinations of Date/Employee for absence days
VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Absence, 'Date'[Workday Index], Absence[Employee ID] ),
        Absence[Day of Absence] = "Yes",
        -- =============================================
        -- *** Updated conditions based on Workday Index
        'Date'[Workday Index] >= MinDateAdj,
        'Date'[Workday Index] <= MaxDateAdj,
		NOT ISBLANK ( 'Date'[Workday Index] ),
        REMOVEFILTERS ( 'Date' )
        -- =============================================
    )
-- Add @PeriodID which uniqely identifies each contiguous absence period per employee
VAR AddPeriodID =
	ADDCOLUMNS (
		AbsenceDays,
		"@PeriodID",
		VAR DateRank =
			RANK ( DENSE, AbsenceDays, ORDERBY ( 'Date'[Workday Index], ASC ), DEFAULT, PARTITIONBY ( Absence[Employee ID] ) )
		VAR Result =
			DateRank - 'Date'[Workday Index]
		RETURN
			Result
	)
-- @DaysPerPeriod is the # days of absence in each period per employee
VAR DaysPerPeriod =
	GROUPBY (
		AddPeriodID,
		[@PeriodID],
		Absence[Employee ID],
		"@DaysPerPeriod", SUMX ( CURRENTGROUP(), 1 ),
        "@PeriodStart", MINX ( CURRENTGROUP (), 'Date'[Workday Index] )
	)
-- Use a join to include only  absences beginning intside filtered date range
VAR EnforceDateFilter =
	NATURALINNERJOIN ( DaysPerPeriod, SELECTCOLUMNS ( VALUES ( 'Date'[Workday Index] ), "@PeriodStart", 'Date'[Workday Index] + 0  ) )

-- Filter to absence periods >= Threshold that begin during period
VAR FilterThreshold =
	FILTER (
		EnforceDateFilter,
        [@DaysPerPeriod] >= Threshold
    )
    
-- Count the resulting absence periods    
VAR Result =
    COUNTROWS ( FilterThreshold )
RETURN
    Result

It looks correct following a quick check, but there might be some edge cases to handle.

[ElliotCartlidge]

Hi Owen,

Something that differs from the original problem is that a single day for me is classed as an absence. So as per your data set, employee 2 would have 3 absences in Jan. Could you adjust so it counts that?

Also quick note, simply copy pasting this in didn't work, i had to adjust the "REMOVEFILTERS ('Date')" part in this to a specfic date column.

VAR AbsenceDays =
    CALCULATETABLE (
        SUMMARIZE ( Absence, 'Date'[Workday Index], Absence[Employee ID] ),
        Absence[Day of Absence] = "Yes",
        -- =============================================
        -- *** Updated conditions based on Workday Index
        'Date'[Workday Index] >= MinDateAdj,
        'Date'[Workday Index] <= MaxDateAdj,
		NOT ISBLANK ( 'Date'[Workday Index] ),
        REMOVEFILTERS ( 'Date' )

Really appreciate the help with this!

[OwenAuger]

Hi again @ElliotCartlidge 

To count all absences of a single day or more, change the Threshold variable to 1:

VAR Threshold =
    1

On your second point, it must be something specific to your model. Do you have a similar setup with the 'Date' table related to the Absence table? But if you have it working already that's fine 🙂

Hope that helps!

[OwenAuger]

@ElliotCartlidge 

Update: Here is an example of how you could transform the Absence table to simplify the measure (PBIX attached).

1. Restructure the Absence table, with one row per contiguous Absence per Employee.

2. Relate Absence[Absence Start] to 'Date'[Date].

3. Create this measure:

# Absence Periods by Start Date v2 = 
VAR Threshold =
    2
VAR Result =
    CALCULATE (
        COUNTROWS ( AbsenceRestructure ),
        KEEPFILTERS ( AbsenceRestructure[Absence Duration] >= Threshold )
    )
RETURN
    Result

Note that you could preserve the original fact table and add this as an additional form of the fact table for specific measures, depending on the reporting requirements.

[NicolaiW]

Hi Owen,
Awesome Work mate! i recon we can ignore the weekends as people rarely work these  days anyway. 

A last small (hopefully) challenge for you. We also want to calculate the average length of these absence periods for each worker. Could you help me out with this as well? 

Thanks beforehand
/Nicolai