DAX Count consecutive days

[siumui]

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 the same ID continue to the new/next weekday, I would want to keep counting just like the data below for 11/16/23, 11/17/23, 11/20/23, 11/21/23 and 11/22/23.

Any help is greatly appreciated!  Thank you

[ThxAlot · Accepted solution]

Add a calendar table and an index column to facilitate the calculation; but it's never for a novice as it leverages embedded table iteration.

(2023/11/23 is marked as non-working day on purpose; therefore 2023/11/24 is calculated as consecutive day)

[siumui]

Hi ThxAlot,

Saying thank you very much to you is simply not enough!  I truly appreciate and being grateful for your help.  It took me a while to create a dynamic calendar with working day and so forth.  Your codes works like a charm!  It turns out I need to count consecutive business days and your codes did it!!!  

Thank you so much for everything!  I have marked your codes as accepted solution.  Have a wonderful day!!  I hope you will continue to help others who need help just like me, getting stuck working on for a couple days and hitting dead end!  And you save the day!!!  THANK YOU!!!!

[siumui]

Hi ThxAlot,

Thank you very much for your help!  I download your file but I'm able to open it.  It says "...is incompatible with your current version of Microsoft Power BI Desktop.  Please install the latest version and try opening the document again."

If you don't mind can you please post the codes for creating the calendar table?  

[ThxAlot]

PQ code for tables: _CALENDAR, ATTENDANCE

// _CALENDAR
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdErDoAwFETRvVST0Jm2fCQCFIqParr/bYAjJNce8fJuptbg6NRLvUIXruNeQ+s+NGEizC9uy37+taAOdGAknAhnQkVU4QMyM5YpoxZUTBO2aeInZmRjn3k1nM2JD2OeeTkPzBhoXM84X/ry2gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Working Day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Working Day", type logical}})
in
    #"Changed Type"

// ATTENDANCE
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67DcAgDAbhXVwjwW8jh8yC2H+NFCgv6dqvuZvTpKqs3jysWM+0VTYegN4IReiEnXAQnoBBdcWNkh7sgO/8F0XohOOH6wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"id", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

DAX for calculated column

Consecutive = 
VAR __dt = ATTENDANCE[date]
RETURN
    COUNTROWS(
        FILTER(
            ATTENDANCE,
            VAR __dt_ = ATTENDANCE[date]
            RETURN
                ATTENDANCE[id] = EARLIER( ATTENDANCE[id] )
                    && ATTENDANCE[date] <= __dt
                    && EARLIER( ATTENDANCE[Index] ) - ATTENDANCE[index] + 1
                        = COUNTROWS(
                            FILTER(
                                _CALENDAR,
                                __dt_ <= _CALENDAR[Date] && _CALENDAR[Date] <= __dt
                                    && _CALENDAR[Working Day]
                            )
                        )
        )
    )

[FreemanZ]

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]

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!!!

[OwenAuger]

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

[siumui]

Hi OwenAuger,

Thank you for taking the time to help me!  I'm greatly appreciate it!  Everyone who post a .pbix in here helping me, I cannot open it due to different version.  I have not work with rank before and this is a chance for me to work with rank from you codes.  Your codes will teach me more and I get to learn new things!  Thank you for everything.  Have a wonderful day!!