I am not sure if this is best done in Power Query or DAX.
Either way, I have no idea how to do it.
I have a table of Agreements and in each of these Agreements I have a series of Jobs. The Jobs are just a continuous series of numbers and there is just one sequence across all agreements.
So in Agreement 1 I might have Job numbers 20 and 50.
In Agreement 2 I might have Jobs 33 and 43.
To make life more complicated, If one of these jobs is cancelled and replanned, a new job is created and there is a reference to the job that was cancelled.
If that is not bad enough, a job can be replanned multiple times. The only sequence is provided by the Replanned Job Number.
My problem is how do I identify the first and last number in each series.
I can say with certainty, that the first job in any sequence will always have a replanned job number of NULL.
So in this example, Agreement number 22674 has 17 jobs, but of these, 8 are simply replans of original jobs.
So in the example that I have attached, 177290 is the first job in the sequence, but the last is 177409.
I’m trying to understand the reasoning behind why Job Number 177290 is considered the first job in the sequence for Agreement 22674 and why Job Number 177409 is considered the last job. From what I can see, the Replanned Job Number column should indicate if a job was created as a result of a prior job being replanned or canceled. In such a sequence, the first job would be one that doesn’t have a Replanned Job Number, and the last job would be the final one that is not referenced as being replanned by any other job in the chain.
Looking at the data, it seems that Job Number 177290 has a Replanned Job Number of 177294, which indicates that it’s actually part of a longer chain of replanned jobs, and Job Number 177409 is directly linked as being a replanned job.
If 177290 is referencing 177294, this would imply that 177290 isn’t the start of the sequence. Similarly, if 177409 is being replanned, it wouldn’t be the last job either.
I’m curious if there’s another factor that is driving this interpretation that I might be missing, but based on my understanding of the data structure, the actual first and last jobs in the sequence would need to be calculated differently—by tracing the chain of Replanned Job Numbers to identify the true start and end.
