Power BI Continuous Streak - Three (Multi) Day Difference 08-25-2020 09:52 AM

[amitchandak]

Continuous streak is needed when you have a set of start and end dates with a difference of a few days. In this case, we take the case with 3 days gap and they need to be combined.

Continuous Flag = var _1 = DATEDIFF(Sheet1[End Date] , MINX(FILTER(Sheet1, ([Start Date]>=EARLIER(Sheet1[End Date])+1 && [Start Date]<=EARLIER(Sheet1[End Date])+3)&& [Group]=EARLIER(Sheet1[Group]) ),([End Date])),DAY) +DATEDIFF( Sheet1[Start Date] -1, maxx(FILTER(Sheet1, ( [end Date]<=EARLIER(Sheet1[Start Date])-1 && [end Date]>=EARLIER(Sheet1[Start Date])-3)  && [Group]=EARLIER(Sheet1[Group])) ,EARLIER([Start Date])),DAY) 

Var _3 = datediff(maxx(FILTER(Sheet1,[End Date]<EARLIER([Start Date])  && [Group] =EARLIER(Sheet1[Group])),[End Date]),[Start Date].[Date],DAY)
return if(_3<=3, BLANK(),1)+0 

Date :

Continuous Date = COALESCE( MINX(FILTER(Sheet1, ([Start Date]>=EARLIER(Sheet1[End Date])+1 && [Start Date]<=EARLIER(Sheet1[End Date])+3) && [Group]=EARLIER(Sheet1[Group]) ),([start Date]))
  ,maxx(FILTER(Sheet1, ( [end Date]<=EARLIER(Sheet1[Start Date])-1 && [end Date]>=EARLIER(Sheet1[Start Date])-3)&& [Group]=EARLIER(Sheet1[Group])) ,EARLIER([End Date])))

Use 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,
not(ISBLANK(_1)),_1,
[Start Date])
return if(ISBLANK(_3),[Start Date],_3)

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

eyJrIjoiZTQ0ZWZjNjYtM2E1YS00MDBiLTgzYmEtYWY5YmE5MjdiY2U0IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9