Help calculating a "Streak" from a Date Column

[AJTK]

Can anyone help me with a measure? I want to calculate the longest streak (in days) between IncidentType O or P (so ignoring all other incident types). Thanks so much! 

Table Name: IncidentTable

IncidentDateIncidentType

11/15/2023	O
8/21/2023	M
7/19/2023	N
6/29/2023	S
6/20/2023	S
5/10/2023	S
4/21/2023	N
4/11/2023	P
4/10/2023	M
4/21/2023	M
3/16/2023	O
3/3/2023	M
3/2/2023	S

[Fowmy]

@AJTK 

What would be the expected result based on your sample data?

[AJTK]

218 days (4/11/23 - 11/15/23). The longest streak between O&P, P&P, or O&O. 

[Fowmy · Accepted solution]

@AJTK 

Create this measure:

LongestStreak = 
VAR __FilteredTable = FILTER( Table05 , Table05[IncidentType] IN {"O","P"} )
VAR __StreakCountTable = 
ADDCOLUMNS(
	__FilteredTable,
	"@Days", 
		VAR __CurrentDate = Table05[IncidentDate]
		VAR __PreviousDate = OFFSET( -1 , ORDERBY( Table05[IncidentDate] ) )
		VAR __Result = 
			IF( NOT ISBLANK(__CurrentDate) && NOT ISBLANK( __PreviousDate ),
				INT( __CurrentDate - __PreviousDate )
			)
		RETURN
			__Result
)
VAR ____LongestStreak = MAXX( __StreakCountTable , [@Days])
RETURN
	____LongestStreak

[AJTK]

Thanks, but I get an error on this part: 

ORDERBY( Table05[IncidentDate] ) )

It says "Parameter is not the correct type." I just replaced "Table05" with my actual table name & column [IncidentDate]

[Fowmy]

@AJTK 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.