Need help to create an event based on comparing two columns and each row of data

[FahQ2022]

Attached excel file contains the data set. 

What I am trying to accomplish in Power BI is  

There is an Entered Data and a Served Date and air craft number 

What I need is a column to say that this group 1 , then group 2 , group 3 etc....

check if f2 is greater than e2 then start with e2 and f2 is maximum

if e3 less than f2 then e2 Is minimum value and f3 is maximum

if e4 less than f3 then e2 is minimum and f4 is greater than f3 , if f4 is less than f3 then f3 still remains maximum

if e5 is greater than the minimum value then e2 is minimum, check if f5 greater than maximum , if not then f3 is maximum

I am trying to attach a file but I can't

so I am entering excel , i am trying to get EVENT column using Dax formula or M code whichever is easy 

Index

Aircraft

DE id

Event

Start

Stop

Duration (days)

Event

minimum

maximum

1

101

DE_101_01

Record_3

1-Jan-22

9-Feb-22

39

Event 1 - 101

e2

f2

check if f2 is greater than e2 then start with e2 and f2 is maximum

2

101

DE_101_01

Record_1

3-Jan-22

17-Feb-22

45

Event 1 - 101

e2

f3

if e3 less than f2 then e2 Is minimum value and f3 is maximum

3

101

DE_101_01

Record_2

9-Jan-22

7-Feb-22

29

Event 1 - 101

e2

f3

if e4 less than f3 then e2 is minimum and f4 is greater than f3 , if f4 is less than f3 then f3 still remains maximum

4

101

DE_101_01

Record_4

5-Feb-22

12-Feb-22

7

Event 1 - 101

e2

f3

if e5 is greater than the minimum value then e2 is minimum, check if f5 greater than maximum , if not then f3 is maximum

5

101

DE_101_02

Record_5

22-Feb-22

3-Mar-22

9

Event 2 - 101

e6

f6

6

101

DE_101_02

Record_7

27-Feb-22

1-Mar-22

2

Event 2 - 101

e6

f6

7

101

DE_101_02

Record_6

1-Mar-22

8-Mar-22

7

Event 2 - 101

e6

f8

8

102

DE_102_01

Record_8

3-Feb-22

7-Feb-22

Event 1 - 102

9

102

DE_102_01

Record_9

4-Feb-22

14-Feb-22

Event 1 - 102

10

102

DE_102_01

Record_10

9-Feb-22

25-Mar-22

Event 1 - 102

[FahQ2022]

Basically , I am trying to capture a downing event. The downing event is when an aircraft is out of service. There are two dates, [Entered Date] and [Servicable Date]

Below are 3 examples of a downing event

Index	Aircraft	Entered Date	Servicable Date	Event		minimum	maximum
1	101	1-Jan-22	9-Feb-22	Event 1 - 101		e2	f2
2	101	3-Jan-22	17-Feb-22	Event 1 - 101		e2	f3
3	101	9-Jan-22	7-Feb-22	Event 1 - 101		e2	f3
4	101	5-Feb-22	12-Feb-22	Event 1 - 101		e2	f3
5	101	22-Feb-22	3-Mar-22	Event 2 - 101		e6	f6
6	101	27-Feb-22	1-Mar-22	Event 2 - 101		e6	f6
7	101	1-Mar-22	8-Mar-22	Event 2 - 101		e6	f8
8	102	3-Feb-22	7-Feb-22	Event 1 - 102
9	102	4-Feb-22	14-Feb-22	Event 1 - 102
10	102	9-Feb-22	25-Mar-22	Event 1 - 102

The minimum and maximum values are the ranges for each downing event which I have put for logic purposes 

For the first record (Index 1)

the assumption is the minimum value is row 2 as mentioned e2 (Entered date) and the maximum value is f2 (Sevicable date) this is the start of the downing event.  

For the second record (Index 2) row 3

(e3) will check if e3 is less than f2 (servicable date) if yes then this record is part of the same downing event and if f2 < f3 then f3 is now the new servicable date instead of f2 for this downing event. So the current range of the downing event went from (1 Jan - 9 Feb) => (Jan 1 - 17 Feb) .  

For the 3rd record (Index 3) 4th row

e4 will check if its less than f3, if yes then this record is part of the same downing event and if f4 (Feb 7 )< f3 ( Feb 17) , current range for the 1st downing event is [e2....f3] [Jan 1 - Feb 17]  

For the 4th (Index 4) record, 5th row

      e5 will check if its less than f3(maximum range point) , if yes then this record is part of the          same downing event and if f5 (Feb 9 )< f3 ( Feb 17) , current range for the 1st downing event is [e2....f3] [Jan 1 - Feb 17]  

For the 5th (Index 5) record, 6th row

      e6 will check if its less than f3(maximum range point) , if yes then this record is part of the          same downing event else new downing event starts as e6 is greater than f3

      So in the event column it will be Event 2 +101 (Aircraft number) and the new range for this        downing event will start from  [e6 ..f6]  (Feb 22 ...... Mar9)  

[v-rzhou-msft]

Hi @FahQ2022 ,

According to your statement, I think your maximum/minimum is calculated based on the previous data. So you need to use looping in your calculation. As far as I know, Dax doesn't support looping. Here I suggest you to try M code to create a custom column in Power Query Editor.

For reference:

Power Query Loop Calculation

List.Generate() and Looping in PowerQuery

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FahQ2022]

Hi Rico

I am not familiar with M , if someone can write the M code it would be greatly appreciated

Thanks

[FreemanZ]

Hi @FahQ2022 

can't follow your description here:

What I need is a column to say that this group 1 , then group 2 , group 3 etc....

check if f2 is greater than e2 then start with e2 and f2 is maximum
if e3 less than f2 then e2 Is minimum value and f3 is maximum
if e4 less than f3 then e2 is minimum and f4 is greater than f3 , if f4 is less than f3 then f3 still remains maximum
if e5 is greater than the minimum value then e2 is minimum, check if f5 greater than maximum , if not then f3 is maximum

can you rephrase it somehow?