Latest Date in same Month

[cmilligan262]

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 Combined Roster'[Employee ID]=EARLIER('2020 Combined Roster'[Employee ID])))

I want to be able to find the latest record within a specific month. If more than one month is selected I want it to show both records with the latest data from the corresponding month.

Thank you

[amitchandak]

Try like

Measure = 
VAR __id = MAX ( '2020 Combined Roster'[Employee ID] )
VAR __date = CALCULATE ( MAX( '2020 Combined Roster'[Date] ), ALLSELECTED ( 'Table' ),  '2020 Combined Roster'[Employee ID] = __id ) 
RETURN CALCULATE ( max ( '2020 Combined Roster'[Date] ), VALUES ( '2020 Combined Roster'[Employee ID] ), '2020 Combined Roster'[Employee ID] = __id, '2020 Combined Roster'[Date] = __date )

[cmilligan262]

@amitchandak So that's not quite working how I need it to. For example since it is not currently march and I have no data for march I want to to show the latest data for that person when I select march

[v-lili6-msft]

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:

If not your case, please share your sample pbix file and your expected output.

Regards,

Lin

[amitchandak]

In case it does not resolve with the last posted solution. Can you share sample data and sample output.

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Mann]

Hi @cmilligan262 

If you have month number or month name in your table then you can use similar calculation with two EARLIER:

Max Date = 
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])))

Create Month Number from Date as a calculated column if you dont have any.

[Month Number]= Month( '2020 Combined Roster'[Date])

Mann.

[cmilligan262]

@Mann That worked great. Thank you. My only issue now is that if there is no data I would like it to pull the most recent data for that person