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

[Pandadev]

Thanks , yes just checked and it does not work , really had jhoped this would not be as difficult as it is proving , thanks for taking time to assist me with this , it is much appreciated

[Pandadev]

still struggling with this , I tried to get a count using this code 

Count =

VAR vCurrentDate = 'Table'[Departure Date]

VAR vCurrentID = 'Table'[ID]

VAR vCurrentCode = 'Table'[Combo]

VAR vPrevTbl =

    FILTER ( 'Table', 'Table'[Departure Date]<= vCurrentDate && 'Table'[ID] = vCurrentID && 'Table'[Combo] = vCurrentCode)

VAR vPrevDate =

    MAXX (

        FILTER (

            vPrevTbl,

            VAR vCD = 'Table'[Departure Date]

            VAR r =

                MAXX ( FILTER ( vPrevTbl, 'Table'[Departure Date] < vCD ), 'Table'[Departure Date] )

            RETURN

                vcd - 1 <> r

        ),

        'Table'[Departure Date]

    )

RETURN

    vCurrentDate - vPrevDate + 1

[Pandadev]

I can confirm that the timestamp is unique for each id

[Pandadev]

This code appears to be working for getting end date , so if i can get start date , then i can date diff both measures 

prev =

VAR prevdate =

    CALCULATE (

        MAX ( 'Table'[Timestamp] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Timestamp] = MAX ( 'Table'[Timestamp] ) - 1 )

    )

VAR currdate =

    CALCULATE (

        MAX ( 'Table'[Timestamp] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Timestamp] = prevdate )

    )

VAR res =

    IF ( MAX ( 'Table'[Timestamp] ) <> prevdate, MAX ( 'Table'[Timestamp] ) )

RETURN

    res

[lbendlin]

this will not work if sequences end and begin on the same date. As I understand the scenario you need to process this on datetime level, not date level.

[Pandadev]

is it possible to show the start date with a measure , the end date with a measure , and also date diff on another measure , that way I can get total consequative counts based on the date diff . 

[lbendlin]

For that you would need to use the windowing functions. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Pandadev]

Here is the raw data 

ID	Departure Date	ATD	Code
21A	14/04/2023	22:01	ASL
21A	15/04/2023	18:05	ASL
21A	16/04/2023	20:57	ASL
21A	17/04/2023	15:15	ASL
21A	17/04/2023	19:34	ASL
21A	17/04/2023	22:01	ASL
21A	18/04/2023	18:05	ASL
21A	18/04/2023	20:57	ASL
21A	19/04/2023	06:30	ASL
21A	19/04/2023	09:27	ASL
21A	19/04/2023	18:35	ASL
21A	19/04/2023	21:13	ASL
21A	20/04/2023	07:22	ASL
21A	20/04/2023	10:01	ASL
21A	20/04/2023	12:43	ASL
21A	20/04/2023	15:53	ASL
21A	20/04/2023	22:20	ASL
21A	21/04/2023	08:33	ASL
21A	21/04/2023	14:17	ASL
21A	21/04/2023	16:28	ASL
21A	21/04/2023	20:12	TVF
21A	21/04/2023	23:06	TVF
21A	22/04/2023	16:34	TVF
21A	22/04/2023	18:08	TVF
21A	22/04/2023	20:01	TVF
21A	23/04/2023	11:20	TVF
21A	23/04/2023	14:40	TVF
21A	23/04/2023	18:10	TVF
21A	23/04/2023	21:05	TVF
21A	24/04/2023	18:39	TVF
21A	25/04/2023	19:39	TVF
21A	26/04/2023	20:39	TVF
21A	27/04/2023	21:39	TVF
21A	28/04/2023	22:39	TVF

and the expected output is 

ID	Code	Start Date	End Date	Date Diff
21A	ASL	14/04/2023	21/04/2023	7
21A	TVF	21/04/2023	28/04/2023	7

[lbendlin]

That is not good sample data. Please provide sample data that covers all scenarios (overlaps, shorter than 7 periods etc)  according to your real life process.

[Pandadev]

ID	Departure Date	ATD	Code
21A	14/04/2023	22:01	AAA
21A	15/04/2023	18:05	AAA
21A	16/04/2023	20:57	AAA
21A	17/04/2023	15:15	AAA
21A	17/04/2023	19:34	AAA
21A	17/04/2023	22:01	AAA
21A	18/04/2023	18:05	AAA
21A	18/04/2023	20:57	AAA
21A	19/04/2023	06:30	AAA
21A	19/04/2023	09:27	AAA
21A	19/04/2023	18:35	AAA
21A	19/04/2023	21:13	AAA
21A	20/04/2023	07:22	AAA
21A	20/04/2023	10:01	AAA
21A	20/04/2023	12:43	AAA
21A	20/04/2023	15:53	AAA
21A	20/04/2023	22:20	AAA
21A	21/04/2023	08:33	AAA
21A	21/04/2023	14:17	AAA
21A	21/04/2023	16:28	AAA
21A	21/04/2023	20:12	AAB
21A	21/04/2023	23:06	AAB
21A	22/04/2023	16:34	AAB
21A	22/04/2023	18:08	AAB
21A	22/04/2023	20:01	AAB
21A	23/04/2023	11:20	AAB
21A	23/04/2023	14:40	AAB
21A	23/04/2023	18:10	AAB
21A	23/04/2023	21:05	AAB
21A	24/04/2023	18:39	AAB
21A	25/04/2023	19:39	AAB
21A	26/04/2023	20:39	AAB
21A	27/04/2023	21:39	AAB
21A	28/04/2023	22:39	AAB
21B	14/04/2023	22:01	BBA
21B	15/04/2023	18:05	BBA
21B	16/04/2023	20:57	BBA
21B	17/04/2023	15:15	BBA
21B	17/04/2023	19:34	BBA
21B	17/04/2023	22:01	BBA
21B	18/04/2023	18:05	BBA
21B	18/04/2023	20:57	BBB
21B	19/04/2023	06:30	BBB
21B	19/04/2023	09:27	BBB
21B	19/04/2023	18:35	BBB
21B	19/04/2023	21:13	BBB
21B	20/04/2023	07:22	BBB
21B	20/04/2023	10:01	BBB
21B	20/04/2023	12:43	BBB
21B	20/04/2023	15:53	BBB
21B	20/04/2023	22:20	BBA
21B	21/04/2023	08:33	BBA
21B	21/04/2023	14:17	BBA
21B	21/04/2023	16:28	BBA
21B	21/04/2023	20:12	BBA
21B	21/04/2023	23:06	BBA
21B	22/04/2023	16:34	BBA
21B	22/04/2023	18:08	BBA
21B	22/04/2023	20:01	BBA
21B	23/04/2023	11:20	BBA
21B	23/04/2023	14:40	BBA
21B	23/04/2023	18:10	BBA
21B	23/04/2023	21:05	BBA
21B	24/04/2023	18:39	BBA
21B	25/04/2023	19:39	BBA
21B	26/04/2023	20:39	BBB
21B	27/04/2023	21:39	BBB
21B	28/04/2023	22:39	BBB
21C	14/04/2023	22:01	CCC
21C	15/04/2023	18:05	CCC
21C	16/04/2023	20:57	CCC
21C	17/04/2023	15:15	CCC
21C	17/04/2023	19:34	CCC
21C	17/04/2023	22:01	CCC
21C	18/04/2023	18:05	CCC
21C	18/04/2023	20:57	CCC
21C	19/04/2023	06:30	CCC
21C	19/04/2023	09:27	CCA
21C	19/04/2023	18:35	CCA
21C	19/04/2023	21:13	CCA
21C	20/04/2023	07:22	CCA
21C	20/04/2023	10:01	CCA
21C	20/04/2023	12:43	CCA
21C	20/04/2023	15:53	CCA
21C	20/04/2023	22:20	CCA
21C	21/04/2023	08:33	CCD
21C	21/04/2023	14:17	CCD
21C	21/04/2023	16:28	CCD
21C	21/04/2023	20:12	CCD
21C	21/04/2023	23:06	CCA
21C	22/04/2023	16:34	CCA
21C	22/04/2023	18:08	CCA
21C	22/04/2023	20:01	CCA
21C	23/04/2023	11:20	CCA
21C	23/04/2023	14:40	CCA
21C	23/04/2023	18:10	CCA
21C	23/04/2023	21:05	CCA
21C	24/04/2023	18:39	CCA
21C	25/04/2023	19:39	CCA
21C	26/04/2023	20:39	CCA
21C	27/04/2023	21:39	CCA
21C	28/04/2023	22:39	CCA

ID	Code	Start Date	End Date	Date Diff
21A	AAA	14/04/2023	21/04/2023	7
21A	AAB	21/04/2023	28/04/2023	7
21B	BBA	14/04/2023	18/04/2023	4
21B	BBB	18/04/2023	20/04/2023	2
21B	BBA	20/04/2023	25/04/2023	5
21B	BBB	26/04/2023	28/04/2023	2
21C	CCC	14/04/2023	19/04/2023	5
21C	CCA	19/04/2023	20/04/2023	1
21C	CCD	21/04/2023	21/04/2023	0
21C	CCA	21/04/2023	28/04/2023	7

[lbendlin]

Power BI will automatically aggregate.  You will need to add a sequence number in Power Query to prevent that from happening.  Otherwise the result would look like:

[lbendlin]

Here is a different approach vith local grouping.

see attached

[Pandadev]

Thanks , I have now managed to get the local grouping added via query editor , so your method is now working , the last thing I need to try and do is only get the latest data based on the ID

so if we look at each ID i would like the following to be shown

21A -- AAB  --   1  -- 4/21/2023 - 4/28/2023 -- 7

21B -- BBB  --   5  -- 4/26/2023 - 4/28/2023  -- 2

21C -- CCA  --   9  -- 4/21/2023 - 4/28/2023 -- 7

[Pandadev]

Thanks for taking a look at this , I added the index , but it is not working for me , maybe due to there being 36,000 ID's , is there another way to get the start and end dates please.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

[Pandadev]

I checked your code and I see that you also grouped the data in power query , I am unable to do this as I bring in data via an incremental refresh , is it possible to replicate what you have as an index by adding a column formula , I tried these formulas , but they are not matching what you have 

i

DateGroupIndex = RANKX('Table','Table'[Departure Date], , ASC, Dense)

NewIndex = RANKX('Table', 'Table'[Code], , ASC, Dense)

[lbendlin]

I cannot help you if you cannot fully describe the scenario. 

Can you guarantee that the Datetime timestamp is unique across all entries for an ID?  If not then you will need some sort of other primary key (which will be rather difficult to implement with incremental refresh)

[Pandadev]

I am not sure , the additional column is time column , so it looks like this 

Code	ID	Date	Depart Time	Day Count
A1	1	15/04/2023	13:20	1
A1	1	15/04/2023	17:15	1
A1	1	16/04/2023	13:20	2
A1	1	19/04/2023	13:20	5
A1	1	19/04/2023	17:15	5
A1	1	20/04/2023	13:20	6
A1	1	21/04/2023	14:20	7
B2	1	22/04/2023	15:20	1
B2	1	23/04/2023	16:20	2
B2	1	24/04/2023	13:20	3
B2	1	24/04/2023	17:20	3
B2	1	25/04/2023	18:20	4
B2	1	26/04/2023	19:20	5
A1	1	27/04/2023	20:20	1
A1	1	29/04/2023	21:20	2
A1	1	30/04/2023	22:20	3
B2	1	01/05/2023	23:20	1
A1	1	02/05/2023	00:20	1
A1	1	03/05/2023	01:20	2
B2	1	04/05/2023	02:20	1
B2	1	05/05/2023	03:20	2
B2	1	06/05/2023	04:20	3
B2	1	10/05/2023	05:20	7
B2	1	10/05/2023	06:20	7
B2	1	10/05/2023	07:20	7
B2	1	10/05/2023	08:20	7

[lbendlin]

Consecutive2 = 
var d = max(Table2[Date])
var c = max(Table2[Code])
var i = max(Table2[ID])
var o = calculate(max(Table2[Date]),ALLSELECTED(Table2),Table2[Date]<d,Table2[Code]<>c,Table2[ID]=i)
var n = CALCULATE(min(Table2[Date]),ALLSELECTED(Table2),Table2[Date]>o,Table2[Code]=c,Table2[ID]=i)
return int(d-n+1)

What should happen when a changeover occurs on the same day?

Like 

A1	1	03/05/2023	01:20
B2	1	03/05/2023	02:20

[Pandadev]

I just realised that the date fields are actually date/time , so there can be more than one event on a day , so the result is sometimes showing 10, 13 , etc , is there a way to fix this please. apologies for not noticing this in my initial request