How to filter records in DAX based on MAX Date

[VladyOselsky]

Hello,

I have a table that stores transaction data regarding items recieved/sold. Depending on selected date I need to filter that table to only the latest record to know what is Total Quantity I have and then calculate number of days it took to sell an Item. 

I'm not sure if it is more appropriate to use a measure or create new table that is filtered? I looked into using TOPN however I'm not getting right data back. I know that I need to calculate MAX(Transaction Date) for each Item and then get record corresponding to that date, however I'm not sure how to proceed forward.

Thanks,

[amitchandak]

@VladyOselsky , Try measure

LASTNONBLANKvalue(Table[Date],Sum(Total[Qty]))

or

calculate(LASTNONBLANKvalue(Table[Date],Sum(Total[Qty])), filter(allselected(Table), Table[Item] = max(Table[Item])))

[VladyOselsky]

@amitchandak That function did not work for me, I ended up creating 3 different measures to produce results that I was looking for but I'm not sure if there a way to optimize them to make it more straight forward.

First Measure just gets me max date from my Calendar table that is used as a slicer

MaxSelectedDate = MAX('Calendar'[Date])

Second Measure gets max date for each item id to be able to get the record I need

MaxItemDate =
VAR _md = [MaxSelectedDate]
VAR _ret =
CALCULATE (
MAX ( TransactionRecords[TransactionDate] ),
ALLEXCEPT ( TransactionRecords, TransactionRecords[ItemID] ),
TransactionRecords[TransactionDate] <= _md
)
RETURN
_ret

Third measure finally return the "Last" value. by filtering the TransactionRecords to specific date

MaxQuantity =
VAR maxD = [MaxItemDate]
VAR _value =
CALCULATE (
MAX ( TransactionRecords[Quantity] ),
ALLEXCEPT ( TransactionRecords, TransactionRecords[ItemID] ),
TransactionRecords[TransactionDate] = maxD
)
RETURN
_value

I was able to spot check and results are correct but it only works if I use ItemID column in a table, If I try to use different column like vendor id to try to summarize by vendor it breaks my logic.