DAX consecutive absent days flag

[sagnik2402]

I have a table in power BI which contains Technician ID and date. There are 870 unique technician IDs and all the dates from 2021 tp 2023 for each ID. We have another column which says whether the technician was absent or present on a particular day. Whenever the technician was absent it is marked as "Gap" and whenever he was present, we say "No Gap". Help me identify the dates falling between long gaps of greater than or equal to 14 days for each technician using DAX. 

[tamerj1]

Hi @sagnik2402 
It is not easy without a sample data to work with therefore, not 100% surre but you may try the following two calculated columns solution

Absence Index =
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Technician ID] ) )
VAR AbsenceTable =
    FILTER ( CurreIDTable, 'Table'[Status] = "Absent" )
VAR Rank1 =
    RANKX ( CurrentIDTable, 'Table'[Date],, ASC, DENSE )
VAR Rank2 =
    RANKX ( AbsenceTable, 'Table'[Date],, ASC, DENSE )
RETURN
    IF ( 'Table'[Status] = "Absent", Rank1 - Rank2 )

Flag > 14 Days =
IF (
    'Table'[Status] = "Absent",
    INT (
        COUNTROWS (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Absence Index] ) )
        ) >= 14
    )
)

[devanshi]

LongGap =
VAR CurrentDate = Table[Date]
VAR PreviousDate = CALCULATE( MAX(Table[Date]), FILTER( ALLEXCEPT(Table, Table[Technician]), Table[Date] < CurrentDate ) )
VAR Gap = IF( ISBLANK(PreviousDate), BLANK(), DATEDIFF(PreviousDate, CurrentDate, DAY) )
RETURN
IF(Gap >= 14, "Gap", "No Gap")