Link rows of data

[M_SBS_6]

Hi, 

I have 4 rows of data and what I want is to pull the min and max of the dates. 

I currently use the following dax:

Earliest Date = 

Calculate (min(test[datetime]), allexcept(test, test[date]))

Latest Date = 

Calculate (max(test[datetime]), allexcept(test, test[date]))

This works fine when I just have the 2 records. 

The problem I have is when more records come in as it takes the Earliest date from the 1st record and the latest from the 4th record. 

What I need is the Earliest date from the first record and latest date from the 2nd record. 

Then the earliest date from the 3rd record and the latest date from the 4th record etc. 

The problem I have is the 2 rows don't have a linked ID to match them together which makes it difficult. 

Long shot but I don't suppose anyone would have an idea how to link based on my logic, maybe index by taking current value and index plus 1? Not sure. 

Example data

Index Datetime Earliest. Latest

1 10:30:30. 10:30:30. 10:32:30

2 10:32:30. 10:30:30. 10:32:30

3 10:45:15. 10:45:15. 10:50:10

4 10:50:10. 10:45:15. 10:50:10

[v-tianyich-msft · Accepted solution]

Hi @M_SBS_6 ,

I grouped them by Even function and you can check the result as follows:

Even = EVEN([ID])

Earliest Date = var _t =ADDCOLUMNS('Table',"Earliest Date",MINX(FILTER(ALL('Table'),[Even]=EARLIER([Even])),[DateTime]))
RETURN MINX(_t,[Earliest Date])

Latest Date = var _t =ADDCOLUMNS('Table',"Latest Date",MAXX(FILTER(ALL('Table'),[Even]=EARLIER([Even])),[DateTime]))
RETURN MAXX(_t,[Latest Date])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[vanessafvg]

please provide the expected solution to your sample data so one is sure what one is testing, is this the exact format of the data?