Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Pandadev
Post Prodigy
Post Prodigy

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

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

CodeIDDate
A1115/04/2023
A1116/04/2023
A1119/04/2023
A1120/04/2023
A1121/04/2023
B2122/04/2023
B2123/04/2023
B2124/04/2023
B2125/04/2023
B2126/04/2023
A1127/04/2023
A1129/04/2023
A1130/04/2023
B2101/05/2023
A1102/05/2023
A1103/05/2023
B2104/05/2023
B2105/05/2023
B2106/05/2023
B2110/05/2023

 

27 REPLIES 27
Pandadev
Post Prodigy
Post Prodigy

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

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
Post Prodigy
Post Prodigy

I can confirm that the timestamp is unique for each id

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

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
Post Prodigy
Post Prodigy

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 . 

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

Here is the raw data 

IDDeparture DateATDCode
21A14/04/202322:01ASL
21A15/04/202318:05ASL
21A16/04/202320:57ASL
21A17/04/202315:15ASL
21A17/04/202319:34ASL
21A17/04/202322:01ASL
21A18/04/202318:05ASL
21A18/04/202320:57ASL
21A19/04/202306:30ASL
21A19/04/202309:27ASL
21A19/04/202318:35ASL
21A19/04/202321:13ASL
21A20/04/202307:22ASL
21A20/04/202310:01ASL
21A20/04/202312:43ASL
21A20/04/202315:53ASL
21A20/04/202322:20ASL
21A21/04/202308:33ASL
21A21/04/202314:17ASL
21A21/04/202316:28ASL
21A21/04/202320:12TVF
21A21/04/202323:06TVF
21A22/04/202316:34TVF
21A22/04/202318:08TVF
21A22/04/202320:01TVF
21A23/04/202311:20TVF
21A23/04/202314:40TVF
21A23/04/202318:10TVF
21A23/04/202321:05TVF
21A24/04/202318:39TVF
21A25/04/202319:39TVF
21A26/04/202320:39TVF
21A27/04/202321:39TVF
21A28/04/202322:39TVF

and the expected output is 

IDCodeStart DateEnd DateDate Diff
21AASL14/04/202321/04/20237
21ATVF21/04/202328/04/20237

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.

IDDeparture DateATDCode
21A14/04/202322:01AAA
21A15/04/202318:05AAA
21A16/04/202320:57AAA
21A17/04/202315:15AAA
21A17/04/202319:34AAA
21A17/04/202322:01AAA
21A18/04/202318:05AAA
21A18/04/202320:57AAA
21A19/04/202306:30AAA
21A19/04/202309:27AAA
21A19/04/202318:35AAA
21A19/04/202321:13AAA
21A20/04/202307:22AAA
21A20/04/202310:01AAA
21A20/04/202312:43AAA
21A20/04/202315:53AAA
21A20/04/202322:20AAA
21A21/04/202308:33AAA
21A21/04/202314:17AAA
21A21/04/202316:28AAA
21A21/04/202320:12AAB
21A21/04/202323:06AAB
21A22/04/202316:34AAB
21A22/04/202318:08AAB
21A22/04/202320:01AAB
21A23/04/202311:20AAB
21A23/04/202314:40AAB
21A23/04/202318:10AAB
21A23/04/202321:05AAB
21A24/04/202318:39AAB
21A25/04/202319:39AAB
21A26/04/202320:39AAB
21A27/04/202321:39AAB
21A28/04/202322:39AAB
21B14/04/202322:01BBA
21B15/04/202318:05BBA
21B16/04/202320:57BBA
21B17/04/202315:15BBA
21B17/04/202319:34BBA
21B17/04/202322:01BBA
21B18/04/202318:05BBA
21B18/04/202320:57BBB
21B19/04/202306:30BBB
21B19/04/202309:27BBB
21B19/04/202318:35BBB
21B19/04/202321:13BBB
21B20/04/202307:22BBB
21B20/04/202310:01BBB
21B20/04/202312:43BBB
21B20/04/202315:53BBB
21B20/04/202322:20BBA
21B21/04/202308:33BBA
21B21/04/202314:17BBA
21B21/04/202316:28BBA
21B21/04/202320:12BBA
21B21/04/202323:06BBA
21B22/04/202316:34BBA
21B22/04/202318:08BBA
21B22/04/202320:01BBA
21B23/04/202311:20BBA
21B23/04/202314:40BBA
21B23/04/202318:10BBA
21B23/04/202321:05BBA
21B24/04/202318:39BBA
21B25/04/202319:39BBA
21B26/04/202320:39BBB
21B27/04/202321:39BBB
21B28/04/202322:39BBB
21C14/04/202322:01CCC
21C15/04/202318:05CCC
21C16/04/202320:57CCC
21C17/04/202315:15CCC
21C17/04/202319:34CCC
21C17/04/202322:01CCC
21C18/04/202318:05CCC
21C18/04/202320:57CCC
21C19/04/202306:30CCC
21C19/04/202309:27CCA
21C19/04/202318:35CCA
21C19/04/202321:13CCA
21C20/04/202307:22CCA
21C20/04/202310:01CCA
21C20/04/202312:43CCA
21C20/04/202315:53CCA
21C20/04/202322:20CCA
21C21/04/202308:33CCD
21C21/04/202314:17CCD
21C21/04/202316:28CCD
21C21/04/202320:12CCD
21C21/04/202323:06CCA
21C22/04/202316:34CCA
21C22/04/202318:08CCA
21C22/04/202320:01CCA
21C23/04/202311:20CCA
21C23/04/202314:40CCA
21C23/04/202318:10CCA
21C23/04/202321:05CCA
21C24/04/202318:39CCA
21C25/04/202319:39CCA
21C26/04/202320:39CCA
21C27/04/202321:39CCA
21C28/04/202322:39CCA

 

IDCodeStart DateEnd DateDate Diff
21AAAA14/04/202321/04/20237
21AAAB21/04/202328/04/20237
21BBBA14/04/202318/04/20234
21BBBB18/04/202320/04/20232
21BBBA20/04/202325/04/20235
21BBBB26/04/202328/04/20232
21CCCC14/04/202319/04/20235
21CCCA19/04/202320/04/20231
21CCCD21/04/202321/04/20230
21CCCA21/04/202328/04/20237

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_0-1682463659076.png

 

Here is a different approach vith local grouping.

lbendlin_1-1682467496138.png

see attached

 

 

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

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.

Please provide sanitized sample data that fully covers your issue.

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)
 

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
Post Prodigy
Post Prodigy

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

CodeIDDateDepart TimeDay Count
A1115/04/202313:201
A1115/04/202317:151
A1116/04/202313:202
A1119/04/202313:205
A1119/04/202317:155
A1120/04/202313:206
A1121/04/202314:207
B2122/04/202315:201
B2123/04/202316:202
B2124/04/202313:203
B2124/04/202317:203
B2125/04/202318:204
B2126/04/202319:205
A1127/04/202320:201
A1129/04/202321:202
A1130/04/202322:203
B2101/05/202323:201
A1102/05/202300:201
A1103/05/202301:202
B2104/05/202302:201
B2105/05/202303:202
B2106/05/202304:203
B2110/05/202305:207
B2110/05/202306:207
B2110/05/202307:207
B2110/05/202308:207

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
Post Prodigy
Post Prodigy

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.