Forum Discussion
siumui
Helper I
Helper IDAX 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...
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)
FreemanZ
Super User
Super Userhi 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!!!