Flag Start of Series of Dates

[Anonymous]

Say for example I was looking to find out the number of instances someone took a period of days off sick...

However the sick days are stored per day rather than giving a start and end date.

How can I flag the start of each period of sick days (1 or zero depending on if the start of a sequence of dates)

Current Table

Date	Employee
24/06/18	A
24/06/18	B
24/06/18	C
25/06/18	A
25/06/18	C
26/06/18	A
26/06/18	D
27/06/18	D

Desired Table

Date	Employee	Flag (Start of Sequence)
24/06/18	A	1
24/06/18	B	1
24/06/18	C	1
25/06/18	A	0
25/06/18	C	0
26/06/18	A	0
26/06/18	D	1
27/06/18	D	0

As you can see the flag indicates 1 if there are no dates (for corresponding employee) in the table before it... or it indicates a zero if there are dates after, depends which way you want to look at it.

Any help would be really appreciated! Thanks!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below to get it:

Flag (Start of Sequence) = 
VAR _preemp =
    CALCULATE (
        MAX ( 'Table'[Employee] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Employee] = EARLIER ( 'Table'[Employee] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    IF ( ISBLANK ( _preemp ), 1, 0 )

Best Regards

[Greg_Deckler]

@Anonymous I am thinking Cthulhu: Cthulhu - Microsoft Power BI Community. Alternatively, perhaps you get the MAXX of the date with matching criteria (EmployeeFlag) where the date is less than the current row's date. Then you check to see if there is only 1 day between them (just use simple subtraction) and then if that number is greater than 1 it is the start of a new sequence. Otherwise, it is not.