Consecutive Days for Employees

[bice_cold]

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

INDEX	ADP ID#	Date	Type
1	A	1/1/2019	sick
2	B	1/1/2019	vacation
3	A	1/2/2019	sick
4	A	1/3/2019	sick
5	B	1/5/2019	vacation
6	A	2/7/2019	vacation
7	A	2/20/2019	sick
8	A	2/21/2019	vacation

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:

INDEX	ADP ID#	Date	Type	consecutive
1	A	1/1/2019	sick	1
2	B	1/1/2019	vacation	0
3	A	1/2/2019	sick	2
4	A	1/3/2019	sick	3
5	B	1/5/2019	vacation	0
6	A	2/7/2019	vacation	0
7	A	2/20/2019	sick	1
8	A	2/21/2019	vacation	0

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:

INDEX	ADP ID#	Date	Type	consecutive
1	A	1/1/2019	sick	1
2	B	1/1/2019	vacation	0
3	A	1/2/2019	sick	1
4	A	1/3/2019	sick	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!

[v-juanli-msft]

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.