How to obtain the max value for several rows with the same id with condition

[SivaSatya]

Hello, I need to calculate in a new colum/new measure the max date for the same id within a table and also check if it is less than today. if yes, that value/date to be taken else check the next max date and check if is less than today and so on.... 

[SivaSatya]

Thank You Kim... It is working fine.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table =

VAR newtable_onorbeforetoday =

FILTER ( Data, Data[Date] <= TODAY () )

VAR groupbytable =

GROUPBY (

newtable_onorbeforetoday,

Data[ID],

"@maxdate", MAXX ( CURRENTGROUP (), Data[Date] )

)

RETURN

CALCULATETABLE ( Data, TREATAS ( groupbytable, Data[ID], Data[Date] ) )

[SivaSatya]

Thank You Kim... New Table is working fine.. Is it possible through new coumn/measure to get this in report? 

[Jihwan_Kim · Accepted solution]

Weight Measure =

VAR currentID =

MAX ( Data[ID] )

VAR newtable_onorbeforetoday =

FILTER ( ALL ( Data ), Data[Date] <= TODAY () && Data[ID] = currentID )

VAR groupbytable =

GROUPBY (

newtable_onorbeforetoday,

Data[ID],

"@maxdate", MAXX ( CURRENTGROUP (), Data[Date] )

)

RETURN

IF (

HASONEVALUE ( Data[ID] ),

CALCULATE (

MAX ( Data[Weight] ),

KEEPFILTERS ( TREATAS ( groupbytable, Data[ID], Data[Date] ) )

)

)