Dax filter required checking change of code by unique ID over a rolling 7 day period

[Pandadev]

Hi I have 16 million events I need to analyze , to identify when a change of code has taken place for a period of 7 consequative days.

events will not always be daily . Below is a an example , showing events for ID 1

so from 15/4/2023 to 21/4/2023 it has a code of A1

then from 22/04/2023 - 26/04/2023  - 5 days it is changed to B2 , as this is not 7 consequative days it is not flagged as a change

then from 04/05/2023 to 10/05/2023 it is showing as B2 for 7 days , so the max date is flagged as a change

Code	ID	Date
A1	1	15/04/2023
A1	1	16/04/2023
A1	1	19/04/2023
A1	1	20/04/2023
A1	1	21/04/2023
B2	1	22/04/2023
B2	1	23/04/2023
B2	1	24/04/2023
B2	1	25/04/2023
B2	1	26/04/2023
A1	1	27/04/2023
A1	1	29/04/2023
A1	1	30/04/2023
B2	1	01/05/2023
A1	1	02/05/2023
A1	1	03/05/2023
B2	1	04/05/2023
B2	1	05/05/2023
B2	1	06/05/2023
B2	1	10/05/2023

[lbendlin]

How do you plan to calculate the day difference with datetime values? That will get messy quickly.

As for the flag - you can do that yourself. Whenever the measure returns 1, and for the previous row it returns 7 or more that is a change flag.

[Pandadev]

thanks , that part of the code works great , all I need to do now is create a measure  , that flags when a code has changed please , if I could get the date of the changed code , and the date of previous event before code changed 

so it would flag the following

A1	1	21/04/2023
B2	1	22/04/2023

[Pandadev]

A DAX measure please

[lbendlin]

see above

[lbendlin]

Can you reformulate your requirement to flag all rows where the code has been present for at least 7 consecutive days?

[Pandadev]

yes that would work , if you could provide me with some example code , I can test it , thanks

[lbendlin]

Power Query or DAX?  Calculated column or measure?

Here is a measure:

Consecutive = 
var d = max('Table'[Date])
var c = max('Table'[Code])
var o = calculate(max('Table'[Date]),'Table'[Date]<d,'Table'[Code]<>c)
var n = CALCULATE(min('Table'[Date]),'Table'[Date]>o,'Table'[Code]=c)
return int(d-n+1)