Forum Discussion

cmilligan262's avatar
cmilligan262
Icon for Helper II rankHelper II
6 years ago

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 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
Need Help

7 Replies

  • Mann's avatar
    Mann
    Icon for Resolver III rankResolver III
    6 years ago

    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's avatar
      cmilligan262
      Icon for Helper II rankHelper II
      6 years ago

      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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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 )