Counting consecutive days (Dax)

[damo230]

Hi,

I am new in DAX and I need help with below:

I have a column with dates and I need a count of consecutive dates, so when days are consecutive it will make cound and when there is a gap then it starts again. to look something like this: 

date	days count
01/01/2024	1
02/01/2024	2
03/01/2024	3
05/01/2024	1
06/01/2024	2
07/01/2024	3
08/01/2024	4
09/01/2024	5
12/01/2024	1
13/01/2024	2
15/01/2024	1

[Anonymous · Accepted solution]

Hi @damo230 

Please try this:
Table:

Click the Transform data in the Home pane.

Then select the Advanced editor, copy the following code and replace all lines of code after Changed Type:

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "IsNewGroup", each if [Index] = 1 then 1 else if Duration.Days(Date.From([Date]) - Date.From(#"Added Index"{[Index]-2}[Date])) = 1 then 0 else 1),
    #"Added Cumulative Group" = Table.AddColumn(#"Added Custom", "Group", each List.Sum(List.FirstN(#"Added Custom"[IsNewGroup], [Index]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Cumulative Group",{"Index", "IsNewGroup"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Group"}, {{"Count", each Table.AddIndexColumn(_, "days count", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "days count"}, {"Date", "days count"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Count",{"Group"})
in
    #"Removed Columns1"

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@damo230 

you can try this to create calculated columns

Column =

 var _last=maxx(FILTER('Table','Table'[date]<EARLIER('Table'[date])),'Table'[date])

 return if ('Table'[date]-_last=1,1,0)

Column 2 =

var _last=maxx(FILTER('Table','Table'[date]<EARLIER('Table'[date])&& 'Table'[Column]=0),'Table'[date])

return if (ISBLANK(_last)||'Table'[Column]=0,1,countx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&'Table'[date]>=_last),'Table'[date]))

[damo230]

In tha calculation you returning 'Days Count' but becasue 'Days Count' is not been yet created 'Days Count' is invalid in this DAX.

[johnt75]

You will need a date table containing all the dates for the years in your data. You can create one with CALENDARAUTO() if you don't already have one. It does not need to be related to your fact table for this calculation.

Day count = 
VAR AllDates = ALL( 'Date'[Date] )
VAR ExistingDates = ALL( 'Table'[date] )
VAR MissingDates = EXCEPT( AllDates, ExistingDates )
VAR CurrentDate = MAX( 'Table'[date] )
VAR PreviousGap = COALESCE( 
	MAXX( FILTER( MissingDates, 'Date'[Date] < CurrentDate ), 'Date'[Date] ),
	MINX( AllDates, 'Date'[Date] ) - 1
)
VAR Result = DATEDIFF( PreviousGap, CurrentDate, DAY )
RETURN Result

[123abc]

To achieve consecutive day counting in Power BI using DAX, follow these steps:

---

1. Prepare the Data

Ensure you have a table (e.g., DatesTable) with at least one column:

• Date (with your date values).

---

2. Create an Index Column

Add an index column to your table to help with sequential calculations. This can be done using Power Query:

1. Go to Power Query Editor.
2. Select your table.
3. Add an Index Column starting from 1:
   • Go to the Add Column ribbon > Index Column > "From 1".

Let's call the table column Index.

---

3. Create a Calculated Column for Consecutive Days

In Power BI, add the following calculated column to your table:

Days Count = VAR PreviousDate = CALCULATE( MAX('DatesTable'[Date]), FILTER( 'DatesTable', 'DatesTable'[Index] = EARLIER('DatesTable'[Index]) - 1 ) ) RETURN IF( 'DatesTable'[Date] = PreviousDate + 1, CALCULATE(MAX('Days Count'), FILTER('DatesTable', 'DatesTable'[Index] = EARLIER('DatesTable'[Index]) - 1)) + 1, 1 )

---

Explanation:

1. PreviousDate finds the date of the previous row by subtracting 1 from the current row's index.
2. It checks if the current date is one day after the PreviousDate:
   • If true, it increments the consecutive day count from the previous row.
   • If false, it resets the count to 1.

---

4. Result

You should get a column (Days Count) that resembles this output:

Date Days Count

01/01/2024	1
02/01/2024	2
03/01/2024	3
05/01/2024	1
06/01/2024	2
07/01/2024	3
08/01/2024	4
09/01/2024	5
12/01/2024	1
13/01/2024	2
15/01/2024	1

---

Notes:

• Ensure your data is sorted by date ascending for the formula to work correctly.
• The Index column helps to reference previous rows efficiently.

Let me know if you encounter any issues! 😊