Apply filter on max date and show value of another column based on the filter

[rehman1]

Hello,

I have a table which looks like shown below. in the last column i want to pull the value which will be the most recent value for column "Monthly_2020" which is "0.43" and the filter appled should be on "Max_Payment" to show most current value. please guide!

Max_Payment	Termed_Members	Active_Flag	Total_Count	Cumulative	Monthly_2020	sum_of_cumulative_2020	Cumulative_2020	Index	Max_Monthly_2020
01/01/2020 0:00	696	Termed	35474	696	1.96%	43976	1.58%	1
02/01/2020 0:00	437	Termed	35063	1133	1.25%	43976	2.58%	2
03/01/2020 0:00	342	Termed	34883	1475	0.98%	43976	3.35%	3
04/01/2020 0:00	245	Termed	35047	1720	0.70%	43976	3.91%	4
05/01/2020 0:00	163	Termed	34994	1883	0.47%	43976	4.28%	5
06/01/2020 0:00	149	Termed	35045	2032	0.43%	43976	4.62%	6

[v-lionel-msft · Accepted solution]

Hi @rehman1 ,

Filter = 
VAR x =  
CALCULATE(
    LASTDATE(Sheet2[Max_Payment]),
    ALL(Sheet2)
)
RETURN
IF(
    MAX(Sheet2[Max_Payment]) = x,
    1, 0
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@rehman1 , try like

calculates(lastnonblankvalue(Date[Date], max(Table[Monthly_2020])), datesmtd(date[date]))

allexcept is another alternative