March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
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
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
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.
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
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 |
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.
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 |
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:
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 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)
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 |
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 |
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
89 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |