topic Re: DAX Count consecutive days in DAX Commands and Tips

DAX Count consecutive days

siumui — Wed, 29 Nov 2023 22:28:23 GMT

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

Re: DAX Count consecutive days

OwenAuger — Thu, 30 Nov 2023 03:34:43 GMT

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

Re: DAX Count consecutive days

FreemanZ — Thu, 30 Nov 2023 06:11:13 GMT

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:

Re: DAX Count consecutive days

ThxAlot — Thu, 30 Nov 2023 09:24:02 GMT

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)

Re: DAX Count consecutive days

siumui — Thu, 30 Nov 2023 18:58:43 GMT

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?

Re: DAX Count consecutive days

ThxAlot — Thu, 30 Nov 2023 23:55:02 GMT

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] ) ) ) )

Re: DAX Count consecutive days

siumui — Mon, 04 Dec 2023 23:12:00 GMT

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

Re: DAX Count consecutive days

siumui — Mon, 04 Dec 2023 23:19:08 GMT

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

Re: DAX Count consecutive days

siumui — Mon, 04 Dec 2023 23:24:53 GMT

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