Dax to obtain count of consecutive days with non consecutive dates

[MBisceglia]

Hi everyone, 

I have a table organized by Date + ID and I  would like to get in a 3rd column a count of consecutive days based on these 2 variables: 

Date	ID	ConsecutiveDaysNum
1/1/2020	52	1
1/2/2020	52	2
1/3/2020	52	3
1/4/2020	52	4
1/8/2020	52	1
1/9/2020	52	2
1/1/2020	789	1
1/2/2020	789	2
1/3/2020	789	3
1/15/2020	789	1

Is there a DAX code for this? Any help will be really appreciated, thanks in advance!

Michelle

[MBisceglia]

It worked like a charm! Thanks rfigtree!

[rfigtree · Accepted solution]

someone is bound to have a more elegant solution, but heres mine.

all calculated columns

isConsecutiveForward = 
var NextEntryDate = CALCULATE(min(TableX[Date]),
                    filter( ALLEXCEPT(TableX,TableX[ID]),
                            TableX[Date]  >EARLIER(TableX[Date])))
return if(NextEntryDate - TableX[Date]=1,TRUE(),FALSE())

isConsequtiveBackward = 
var PreviousEntryDate = CALCULATE(max(TableX[Date]),
                        filter(ALLEXCEPT(TableX,TableX[ID]),
                               TableX[Date] <EARLIER(TableX[Date])))
return if( TableX[Date]-PreviousEntryDate=1,TRUE(),FALSE())

ConsDays=
var StartOfSeq=CALCULATE(MAX(TableX[Date]),
               FILTER(ALLEXCEPT(TableX,TableX[ID]),
                      [isConsecutiveForward] && 
                      not([isConsecutiveBackward])&&
                      TableX[Date] < EARLIER(TableX[Date])))
var Seq=CALCULATE(COUNTROWS(TableX),
                  FILTER(ALLEXCEPT(TableX,TableX[ID]),
                        TableX[Date]<=EARLIER(TableX[Date])&&
                        TableX[Date] >=StartOfSeq))
return SWITCH(TRUE(),
           NOT([isConsecutiveBackward]||[isConsecutiveForward]),1,
           [isConsecutiveForward]&& not([isConsecutiveBackward]),1,
           Seq)