Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
2 years ago
Solved

Link rows of data

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.