Forum Discussion

astronauta49's avatar
astronauta49
Helper 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.

Thanks a lot

 

IDUDATETRIM
12/04/2021AAAA
26/04/2021AAAA
210/04/2021BBB
214/04/2021BBB
318/04/2021CCC
422/04/2021CCC
  • 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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper 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

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    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