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
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.