Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.


Power BI Continuous Streak - With One-Day Break

Problem Description:

There is a set within a group with start and end dates. When the end date and next start date have a gap of only one day, these dates need to be combined.

Note: In HR, these one-day gap sets can be employee leave start and end date.


Solution: For the solution, we need to create the partition of the continuous dates.


1. Create a flag to check that the dates are continuous. This flag needs to be bi-directional for a statement/line to check only 1-day gap on either side. We will get two columns: 



Continuous Flag = var _1 = DATEDIFF(Sheet1[End Date]+1 , MINX(FILTER(Sheet1, ([Start Date]=EARLIER(Sheet1[End Date])+1 )&& [Group]=EARLIER(Sheet1[Group]) ),[Start Date]),DAY) +DATEDIFF( Sheet1[Start Date] -1, maxx(FILTER(Sheet1, ( [end Date]=EARLIER(Sheet1[Start Date])-1)  && [Group]=EARLIER(Sheet1[Group])) ,[End Date]),DAY) 
// var _2=[Date Rank] -  maxx(FILTER(Sheet1, ( [Date Rank]=EARLIER([Date Rank])-1) ) && [Group]=EARLIER(Sheet1[Group])), [Date Rank])
Var _3 = datediff(maxx(FILTER(Sheet1,[End Date]<EARLIER([Start Date])  && [Group] =EARLIER(Sheet1[Group])),[End Date]),[Start Date].[Date],DAY)
return  _1 +if(_3<=1, BLANK(),1)+0







Continuous Date = MAX( MINX(FILTER(Sheet1, ([Start Date]=EARLIER(Sheet1[End Date])+1 ) && [Group]=EARLIER(Sheet1[Group]) ),[Start Date]-1)
  ,maxx(FILTER(Sheet1, ( [end Date]=EARLIER(Sheet1[Start Date])-1)&& [Group]=EARLIER(Sheet1[Group])) ,[End Date]+1))




2. Using the above measures to calculate Start Date:



New Start Date = Var _1 = maxx(FILTER(Sheet1,[Group] =EARLIER(Sheet1[Group])  && [Continuous Date] <=EARLIER(Sheet1[Continuous Date])&& not(ISBLANK([Continuous Date])) && [Continuous Flag]<>EARLIER([Continuous Flag])),Sheet1[Start Date])
Var _2 = minx(FILTER(Sheet1,[Group] =EARLIER(Sheet1[Group]) && [Continuous Date] <=EARLIER(Sheet1[Continuous Date]) && [Continuous Flag]=EARLIER([Continuous Flag]) && not(ISBLANK([Continuous Flag])) ),Sheet1[Start Date])
Var _3 =SWITCH(True(),
[Continuous Flag]=1,[Start Date],
(ISBLANK(_1) && [Continuous Flag]=0),_2,
[Start Date])
return if(ISBLANK(_3),[Start Date],_3)




3. Calculate an End Date with help from Start Date:



New End Date = maxx(FILTER(Sheet1,[Group]=EARLIER([Group]) && [New Start Date]=EARLIER([New Start Date])),[End Date])



Screenshot 2020-08-12 16.54.08.png


This is how final merged rows, look like:


Screenshot 2020-08-12 16.54.20.png

Let us know what you think about these measures. Share your thoughts on different use cases of the continuous streak.


You can get all my posts at


You can find the file at