Counting consecutive days (Dax)

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:

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

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

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

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

Anonymous 

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