Forum Discussion

siumui's avatar
siumui
Icon for Helper I rankHelper I
2 years ago
Solved

DAX Count consecutive days

  Hi,   I’m new to Dax.   For each ID I would like to add 1 column name Count.  This would count consecutive days and reset to 1 if not consecutive.  My data do not have weekends date.  So if th...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

hi siumui ，

 

not sure if i fully get you, try to

1) add an index column, preferably with Power Query

how: https://learn.microsoft.com/en-us/power-query/add-index-column

 

2) add a calculated column like:

 

count2 = 
VAR _table =
ADDCOLUMNS(
    data,
    "col",
    VAR _date = [date]
    VAR _datepre =
    MAXX(
        FILTER(
            CALENDAR(MIN(data[date])-3, MAX(data[date])),
            WEEKDAY([date], 2) IN {1, 2,3, 4, 5}
                &&[date]<_date
        ),
        [date]    
    )
    VAR _dateofindexpre =
    MAXX(
        FILTER(
            data,
            data[index] = EARLIER(data[index]) -1
        ),
        data[date]
    )
    VAR _result =
    IF(    
        _dateofindexpre IN {_datepre, _date -1},
        0,
        1
    )
    RETURN _result
)
VAR _segstartdate =
MAXX(
    FILTER(
        _table,
        data[id]=EARLIER(data[id])
            &&data[date]<=EARLIER(data[date])
            &&[col]=1
    ),
    data[date]
)
VAR _result = DATEDIFF(_segstartdate, [date], DAY)+1
RETURN _result

 

 

it worked like:

siumui's avatar
siumui
Icon for Helper I rankHelper I
2 years ago

Hi FreemanZ,

 

Thank you very much for your help.  I'm greatly appreciate!  It turns out I need to count consecutive business days.  ThnxAlot codes work perfect, counting consecutive business days.

Thank you FreemanZ!!!