Forum Discussion

astronauta49's avatar
astronauta49
Icon for Helper I rankHelper I
5 years ago
Solved

First value by date

Hello. Can you help me, please ? I need a table with the same column, with unique (not duplicate) values from IDU column ... filtered by DATE column. I need the first row (date) of each IDU. Than...
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

    astronauta49 

     

    Here's the calculated table you want:

    [New Table] = // This is a calculated table
// Let the table from your initial post
// be called T (you just substitute the
// name you've got in your model).
var IDUsWithFirstDates =
    ADDCOLUMNS(
        DISTINCT( T[IDU] ),
        "@FirstDate",
            CALCULATE(
                MIN( T[Date] )
            )
    )
var Result =
    CALCULATETABLE(
        T,
        TREATAS(
            IDUsWithFirstDates,
            T[IDU],
            T[DATE]
        )
    )
RETURN
    Result

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

astronauta49 , Create a new measure for TRIM  and use min for date if needed

 

new TRIM =
VAR __id = MAX ('Table'[Name] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Name] = __id )
CALCULATE ( max ('Table'[TRIM] ), VALUES ('Table'[Name] ),'Table'[Name] = __id,'Table'[Date] = __date )

astronauta49's avatar
astronauta49
Icon for Helper I rankHelper I
5 years ago

Sorry, it took me a long time to reply. Thank you very much for your answer. I have not solved my problem yet. I don't understand the code. Do you need to put it in a new measure or column? I'll keep doing tests. Thanks