How to count consecutive dates including 1 day gaps

[jbossuyt]

Hi all,

I have a dataset of antiobiotic consumption per patient

ID             Date        Antiobiotic

Patient A May15      Penicillin

Patient A May 16     Penicillin

Patient A May 18     Penicillin

Patient A May 19     Penicillin

Patient B June 3       Amoxicilin
Patient B June 4       Amoxicilin

Patient B June 6       Amoxicillin

Patient B June 8       Amoxicillin

Patient B June 15     Amoxiclin

Patient B   June 16     Amoxicilin

What I need is to count the consecutive days antibiotic therapy is given to a patient. Sometimes there is a 1 day gap. The counting should ignore this gap and keep counting as if the gap was filled. But a gap of more than one day should be considered a true gap, the next sequence of dates should then be considered as a new counting.

So I would need a clustering per patient and per antiobotic, that counts the consecutive days the antibiotic is given, but keeps counting when there is a 1 day gap (ans also counts the gap as one day)

Desired result (the column "index" is added to identify the different periods of antibiotic therapy per patient per antiobiotic that are seperated in time by more than one day.)

ID             Antibiotic        Number of days    Index

Patient A  Penicillin              5 days                 1

Patient B   Amoxicillin          6 days                 1

Patient B   Amoxicillin          2 days                 2

Thank you in advance!

Jelle

[ThxAlot]

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to crate a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is, as the first step, to create a calculated column in data table.

OFFSET function (DAX) - DAX | Microsoft Learn

index_cc = 
VAR _prevdate =
    ADDCOLUMNS (
        data,
        "@prevdate",
            MAXX (
                OFFSET (
                    -1,
                    data,
                    ORDERBY ( data[date], ASC ),
                    ,
                    PARTITIONBY ( data[id], data[antibiotic] ),
                    MATCHBY ( data[id], data[date], data[antibiotic] )
                ),
                data[date]
            )
    )
VAR _condition =
    ADDCOLUMNS (
        _prevdate,
        "@condition",
            SWITCH (
                TRUE (),
                ISBLANK ( [@prevdate] ), 0,
                INT ( data[date] - [@prevdate] ) <= 2, 0,
                1
            )
    )
RETURN
    SUMX (
        FILTER (
            _condition,
            data[id] = EARLIER ( data[id] )
                && data[antibiotic] = EARLIER ( data[antibiotic] )
                && data[date] <= EARLIER ( data[date] )
        ),
        [@condition]
    ) + 1

The second step is to add relevant information to the table visualization and create a measure that shows Number of days.

INDEX function (DAX) - DAX | Microsoft Learn

Number of days: = 
VAR _t =
    SUMMARIZE ( data, 'calendar'[Date] )
VAR _groupstartdate =
    MAXX ( INDEX ( 1, _t, ORDERBY ( 'calendar'[Date], ASC ) ), 'calendar'[Date] )
VAR _groupenddate =
    MAXX ( INDEX ( 1, _t, ORDERBY ( 'calendar'[Date], DESC ) ), 'calendar'[Date] )
RETURN
    IF (
        HASONEVALUE ( 'id'[id] ),
        DATEDIFF ( _groupstartdate - 1, _groupenddate, DAY )
    )