Get First and Last Rows with all Columns

[smerchant]

I have data something like this and I want get the first and last rows based on the date:

I created some measure for example like InitialScore, LastScore, FirstDate...: 

InitialScore = 
MINX(
    FILTER(
        ALL(Sheet1),
        [CatIns] = EARLIER([CatIns]) 
        && [Date] = EARLIER(Sheet1[InitialTestDate])),
        [Score])

And got results like this which is correct but..:

I have score2, 3 and more columns in the real data and I don't think should be creating measures for each column?  Is there a way to get all the rows filtered by first and last row by Category (CatIns)?

Thanks,

[parry2k]

@smerchant measure or column will only return scalar value not a row

[smerchant]

Thanks.  I might have failed to explain.  I need to get all the columns and only first and last row by category based on the date (first and lastest)

I made another attempt through an identifier column. Not sure if this is most appropriate way, although, I do get the empty row on top which I suppose I can filter out.

Identifier = 
IF (
    MIN ( Sheet1[Date] ) = Sheet1[FirstTransactionDate],
    "First Test",
    IF (
        MAX ( Sheet1[Date] ) = Sheet1[LastTransactionDate],
        "Last Test",
        BLANK ()
    )
)

This attempt also give the wrong sum for score columns

[parry2k]

@smerchant issue with your question is to get all columns in a row, it is possible to get a column, not a problem, and you have to perform the same calculation for all the columns. 

[smerchant]

Thanks.  Even if you have 20 columns?  is it possible to create a seperate table based on the condition/filter which gets updated?

[Anonymous · Accepted solution]

HI @smerchant ,

You can consider to do unpivot columns on your fields, then you can simply write formula to calculate first/last records based on original category and attribute group.

Unpivot columns (Power Query)

Regards,

Xiaoxin Sheng