how to group the column and get the value based on smallest date in PowerBi

Question

I am trying to make a new column which will give the result from a column based on smallest date from another column. I can find minimum date using (calculate(min(Date),allexcept(table[Id]))

 ID     Date    Value   Result
192 23/4/2019   A1  A1
192 24/4/2020   A2  A1
192 25/4/2021   A3  A1
192 26/4/2022   A1  A1
193 27/4/2023   A2  A2
193 28/4/2024   A1  A2
193 29/4/2025   A3  A2

in this result column needs only Value is date is smallest of same Id. Thanks.

score 0 · Accepted Answer · answered Dec 10 '19 at 08:10

You can store the calculated Min Date in a variable, then use that in a filter. So your calculated column becomes:

Value on Min Date = 
VAR MinDate = 
    CALCULATE ( 
        MIN (MyTable[Date] ),
        MyTable[ID] = EARLIER ( MyTable[ID] )
    )
RETURN
    CALCULATE ( 
        FIRSTNONBLANK ( MyTable[Result], 1 ),
        MyTable[ID] = EARLIER ( MyTable[ID] ),
        MyTable[Date] = MinDate
    )

how to group the column and get the value based on smallest date in PowerBi

1 Answers1