Latest Date in same Month

I currently use a calculated column to find the most recent date in a table for a specific person. Max Date = calculate(Max('2020 Combined Roster'[Date]),Filter('2020 Combined Roster','2020 Comb...

Need Help

v-lili6-msft

Community Support

6 years ago

hi cmilligan262

You could try this formula

Max Date = 
var _value=CALCULATE(MAX('2020 Combined Roster'[Date]),
FILTER('2020 Combined Roster',
'2020 Combined Roster'[Employee ID]=EARLIER('2020 Combined Roster'[Employee ID]) &&
'2020 Combined Roster'[Month Number]=EARLIER('2020 Combined Roster'[Month Number])))
return
IF(ISBLANK('2020 Combined Roster'[Value]),
CALCULATE(MAX('2020 Combined Roster'[Date]),
FILTER('2020 Combined Roster',
'2020 Combined Roster'[Employee ID]=EARLIER('2020 Combined Roster'[Employee ID]) &&'2020 Combined Roster'[Date]<EARLIER('2020 Combined Roster'[Date]))),
_value)

Result: