cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dphillips Member
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 YearSemImage1.JPG

 

 

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.

 

Image2.JPG

 

 

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
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]))
            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

3 REPLIES 3
Phil_Seamark Super Contributor
Super Contributor

Re: Filter data using last value in a table

Hi @dphillips

 

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))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
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]))
            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

dphillips Member
Member

Re: Filter data using last value in a table

Brilliant - thanks for your help

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 33 members 833 guests
Please welcome our newest community members: