Power BI measure based on last column value and filter

Question

I have a table with records of some people's weights:

Year    Person  Weight
2010    Mike    75
2010    Laura   60
2011    Mike    80
2011    Laura   55
2012    Laura   58

I want to create a Measure to find out the average weight based on the last weight of each person:

• Report Year = no filter => Mike = 80, Laura = 58, Average = 69
• Report Year = 2011 => Mike = 80, Laura = 55, Average = 67.5
• Report Year = 2010 => Mike = 75, Laura = 60, Average = 67.5

bearing in mind that the weight can go up/down over time, so functions like MAXX or MINX won't help. The most logical I could find was this:

lastWeight = LASTNONBLANK(Weights[Weight],FILTER(Weights,Weights[Person]=Weights[Person]))

But it's complaining that it returns multiple columns. It seems simple but I'm just completely stuck.

Any idea how I can create the measure to have the last weight value for each person?

Many thanks.

PS: initially I thought a column would be a good idea, but it's not, as I want to see the last weight for a given period of time, so it will be a measure calculated on the fly based on my report time range

Answer 1

This is the data that I used :-

Year    Person  Weight
2010    Mike    75
2010    Laura   60
2011    Mike    80
2011    Laura   55
2012    Laura   58
2010    Mike    50
2010    Laura   51
2011    Mike    52
2011    Laura   53
2012    Laura   54
2010    Mike    99
2010    Laura   100
2011    Mike    101
2011    Laura   102
2012    Laura   103

I first created an Index column by going into edit queries

So, once I created the Index Column - I then created a measure called as

Last Weight in the Data = LASTNONBLANK(Table2[Weight],MAX(Table2[Index]))

So now, I can use this measure on my visuals :-

For example:-

Let me know, if this doesn't help.