How to return value of a field based on Latest Record Date

[Alice_Cooper]

Hi Guys,

I have come across a problem and would really like some help.

I have data set HLPropertyDataEntry three fields are in question here. EndOfMonthReportPeriod, TotalProperties and TotalActiveProperties .... The visualisation is not filtered. The visualisation returns data from two other datasets that contain only one record (HLPartyDataEntry and HLFundingDataEntry). The values for these in the snippet below are 100, 85 and the $1,475,629 ... These are OK.

The Datatset HLPropertyDataEntry is the problem... I want to display a Card that gives me the Latest values of TotalProperties and TotalActiveLeases based on EndOfMonthReportPeriod.

The data is as per the Matrix where September 2023 should have 32 as Total Properties and 25 as TotalActiveLeases ... When using the Card Viz for TotalActiveLeases I cannot get the latest record ... using Max returns 28 ... Can someone help me please?

Here is teh data:

Year	Month	Total Properties	Total Active Leases
2022	July	3	3
2022	August	5	5
2022	September	8	8
2022	November	10	10
2023	January	13	12
2023	February	15	14
2023	March	19	18
2023	April	21	20
2023	May	24	23
2023	June	26	24
2023	July	29	26
2023	August	31	28
2023	September	32	25

[Alice_Cooper]

Fantastic.... I am very appreciative... Have been working on this for days ... Works a treat .... You are a Legand.... 

[Greg_Deckler · Accepted solution]

@Alice_Cooper Assuming you have a Date column, try:

Measure = 
  VAR __Date = MAXX(ALL('Table'),[Date])
  VAR __MaxValue = MAXX(FILTER(ALL('Table'),[Date] = __Date),[Total Active Leases])
RETURN 
  __MaxValue