cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bice_cold Regular Visitor
Regular Visitor

Consecutive Days for Employees

I have a table named [Attendance] that looks like this:

 

INDEXADP ID#DateType
1A1/1/2019sick
2B1/1/2019vacation
3A1/2/2019sick
4A1/3/2019sick
5B1/5/2019vacation
6A2/7/2019vacation
7A2/20/2019sick
8A2/21/2019vacation

 

I also have a relationship set up to a date table.

 

I need to get the value of consecutive days for each time the employee is out sick. A measure would be great, but if a calculated column works I would happily accept that as a solution. My expected output in a table would look like this:

 

INDEXADP ID#DateTypeconsecutive
1A1/1/2019sick1
2B1/1/2019vacation0
3A1/2/2019sick2
4A1/3/2019sick3
5B1/5/2019vacation0
6A2/7/2019vacation0
7A2/20/2019sick1
8A2/21/2019vacation0


Right now, I have a column that gives me a value if the day is consecutive sick day or not, but I'm not sure how to aggregate it for the desired output. It looks like this:

INDEXADP ID#DateTypeconsecutive
1A1/1/2019sick1
2B1/1/2019vacation0
3A1/2/2019sick1
4A1/3/2019sick

1

 

Here's what I have so far -

 

Is Consecutive = 

// This column determines if the occurrence is consecutive

var adpid = AttendanceLog[ADP ID#]
var rowDate = AttendanceLog[Date]

var previousWorkday =
CALCULATE(
    MAX('Date'[Date]),
        FILTER('Date',
            'Date'[Date] < rowDate
            && 'Date'[IsWorkDay] = 1
        )
)

// determine if the last workday was an occurrence or not
// if the previous day has a value for unexcused absence and is not scheduled, it will return the index
var lastOccurrenceCheck =
MAXX(
    FILTER(AttendanceLog,
        AttendanceLog[ADP ID#] = adpid
        && AttendanceLog[Date] = previousWorkday
        && AttendanceLog[Absence Code] <> "scheduled"
    ),
    AttendanceLog[Index]
)

// if the row occurrence is a consecutive sick or unscheduled day, give it a value of 1. Otherwise, don't give it a value.
var isConsecutive =
IF(
    ISBLANK(lastOccurrenceCheck),
    0,
    1
)


return

isConsecutive

Thanks in advance for any help I can get!

1 REPLY 1
Community Support Team
Community Support Team

Re: Consecutive Days for Employees

Hi @bice_cold 

It would be easier with Power query. Please check the method if it fits your scenario.

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

https://community.powerbi.com/t5/Desktop/dax-grouping-consecutive-days/td-p/488880

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 169 members 3,387 guests
Please welcome our newest community members: