cancel
Showing results for
Did you mean:
Member

## Filter data using last value in a table

I have some values and I find the average of these values over a field called YearSem

I use a line graph to visualise this. See below.

How can I pick out the last value (3.49) and put it into a card?

At the moment I find the last value in the YearSem column by creating this measure:

Last LP Period = CALCULATE(MAX(AveLPs[YearSem]))

This works and I get a value which is 2017 (T4).

Then I use this in a measure like this:

Last LP Ave = CALCULATE(AVERAGE(AveLPs[AveLP]),FILTER(AveLPs,AveLPs[YearSem]=[Last LP Period]))

However, I still get the average for all the years (3.58), NOT that last value (3.49) when I put this measure into a card.

What am I doing wrong? Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Filter data using last value in a table

This would also work.  The issue with your query is when you bring a calculated measure through to the CALCULATE you change the filter context (which sometimes is what you want - but not in your case)

```Last LP Ave =
CALCULATE(
AVERAGE(AveLPs[Average of AveLP]),
FILTER(AveLPs,AveLPs[YearSem]=MAX(AveLPs[YearSem]))
)```

Proud to be a Datanaut!

3 REPLIES 3
Super Contributor

## Re: Filter data using last value in a table

You have a filter context problem that can be solved by re-arranging your measure to be as follows

```Last LP Ave =
var d = CALCULATE(MAX(AveLPs[YearSem]))
return
CALCULATE(
AVERAGE(AveLPs[Average of AveLP]),
FILTER(AveLPs,AveLPs[YearSem]=d))```

Proud to be a Datanaut!

Super Contributor

## Re: Filter data using last value in a table

This would also work.  The issue with your query is when you bring a calculated measure through to the CALCULATE you change the filter context (which sometimes is what you want - but not in your case)

```Last LP Ave =
CALCULATE(
AVERAGE(AveLPs[Average of AveLP]),
FILTER(AveLPs,AveLPs[YearSem]=MAX(AveLPs[YearSem]))
)```

Proud to be a Datanaut!

Member

## Re: Filter data using last value in a table

Brilliant - thanks for your help

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 33 members 833 guests
Recent signins: