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

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's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity 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:

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

     

    Regards,

    Lin

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

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