DAX Count consecutive days

Hi siumui 

Here's an example of how Count could be computed in a calculated column (PBIX attached).

Count =
-- Summarize DATE & ID combinations for current ID
VAR SummaryDateID =
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[DATE], Data[ID] ),
        ALLEXCEPT ( Data, Data[ID] )
    ) -- Get current Date
VAR CurrentDate = Data[DATE] -- Get earliest Date for current ID
VAR MinDatePerID =
    MINX ( SummaryDateID, 'Data'[DATE] ) -- @RunID uniquely identifies the current run
-- It is defined as the difference between
--   1. The workday count from MinDatePerID and current row's date; and
--   2. The "rank" of a given date 
VAR AddRunID =
    ADDCOLUMNS (
        SummaryDateID,
        "@RunID",
            VAR WorkdayIndex =
                NETWORKDAYS ( MinDatePerID, Data[DATE] )
            VAR DateRank =
                RANK (
                    DENSE,
                    SummaryDateID,
                    ORDERBY ( Data[DATE], ASC ),
                    DEFAULT,
                    PARTITIONBY ( Data[ID] )
                )
            RETURN
                WorkdayIndex - DateRank
    ) -- Retrieve the RunID for the current row's Date.
VAR CurrentRunID =
    SELECTCOLUMNS ( FILTER ( AddRunID, 'Data'[DATE] = CurrentDate ), [@RunID] ) -- Retrieve the rows for the current run.
VAR CurrentRun =
    FILTER ( AddRunID, [@RunID] = CurrentRunID ) -- Compute the rank of the current row's Date within the current run
VAR Result =
    RANK ( DENSE, CurrentRun, ORDERBY ( Data[DATE], ASC ) )
RETURN
    Result

There could be some simplifications possible but at least this is a working calculation.

 

Regards

 

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: