Forum Discussion

karinafreitass's avatar
karinafreitass
Helper III
5 years ago
Solved

Most recent status

Hi Guys,   Could you help me, please?!   As you can see, the measure below returns me the status of the ID with the most recent date, but I came across this situation where I have two equal dates...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    karinafreitass , Not very clear with you screen shot, You can get latest status using a measure like

     


    Measure =
    VAR __id = MAX ('Table'[ID] )
    VAR __date = CALCULATE ( MAX('Table'[NUM] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )

    return
    CALCULATE ( MAX ('Table'[Status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[NUM] = __date )

     

    or a new column

     

    Measure =
    VAR _date = MAXX(Filter(Table, [ID] = earlier([ID])), [Date])
    return
    MAXX(Filter(Table, [ID] = earlier([ID]) && [Date] =_date), [status])

     

     

amitchandak's avatar
amitchandak
Super User
5 years ago

karinafreitass , Not very clear with you screen shot, You can get latest status using a measure like

 


Measure =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[NUM] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )

return
CALCULATE ( MAX ('Table'[Status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[NUM] = __date )

 

or a new column

 

Measure =
VAR _date = MAXX(Filter(Table, [ID] = earlier([ID])), [Date])
return
MAXX(Filter(Table, [ID] = earlier([ID]) && [Date] =_date), [status])