topic Re: DAX | Count consecutive days of absence in DAX Commands and Tips

DAX | Count consecutive days of absence

NicolaiW — Tue, 15 Aug 2023 07:47:05 GMT

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

Re: DAX | Count consecutive days of absence

OwenAuger — Tue, 15 Aug 2023 12:16:30 GMT

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

Re: DAX | Count consecutive days of absence

NicolaiW — Tue, 15 Aug 2023 19:46:32 GMT

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

Re: DAX | Count consecutive days of absence

OwenAuger — Wed, 16 Aug 2023 12:47:08 GMT

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:

Employee 4 indeed has 1 absence ignoring weekends.
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.
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

Re: DAX | Count consecutive days of absence

NicolaiW — Wed, 16 Aug 2023 18:07:05 GMT

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

Re: DAX | Count consecutive days of absence

OwenAuger — Thu, 17 Aug 2023 05:37:29 GMT

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

Re: DAX | Count consecutive days of absence

NicolaiW — Thu, 17 Aug 2023 15:29:28 GMT

Thanks Mate,

I'm out of Questions now 😀

/Nicolai

Re: DAX | Count consecutive days of absence

ElliotCartlidge — Wed, 26 Mar 2025 15:22:50 GMT

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.

Re: DAX | Count consecutive days of absence

OwenAuger — Thu, 27 Mar 2025 01:38:22 GMT

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

Re: DAX | Count consecutive days of absence

OwenAuger — Thu, 27 Mar 2025 04:43:35 GMT

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.

Re: DAX | Count consecutive days of absence

ElliotCartlidge — Thu, 27 Mar 2025 08:43:11 GMT

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.

Re: DAX | Count consecutive days of absence

OwenAuger — Sun, 30 Mar 2025 23:00:51 GMT

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?

Re: DAX | Count consecutive days of absence

ElliotCartlidge — Mon, 31 Mar 2025 07:12:29 GMT

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

Re: DAX | Count consecutive days of absence

OwenAuger — Mon, 31 Mar 2025 21:37:25 GMT

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.

Re: DAX | Count consecutive days of absence

ElliotCartlidge — Wed, 02 Apr 2025 08:28:59 GMT

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!

Re: DAX | Count consecutive days of absence

OwenAuger — Wed, 02 Apr 2025 18:19:54 GMT

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!