cancel
Showing results for
Did you mean:
Frequent Visitor

## Find a value associated with the latest date

I have a list of mileage reads and want to get the reading associated with the latest mileage date.

Because mileages are sometimes corrected I cannot simply grab the highest mileage reading because that value may not be accurate. For example, in the screen shot below we see the correct mileage for this vehicle is 106598 taken on 8/20. However, the highest reading is 140357 taken on 8/9.

What I want is a measure to give the different of the earliest and latest readings. Something like this but that does not use the max of meter but that uses the meter reading associted with the latest date Query1[METER_DATE].

Usage Measure = MAX(Query1[METER]) - MIN(Query1[METER])

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Find a value associated with the latest date

Hi,

This measure works

`Measure = CALCULATE(SUM('Sheet 1'[METER]),LASTDATE('Sheet 1'[METER_DATE]))`

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
10 REPLIES 10
Resolver II

## Re: Find a value associated with the latest date

Hey @cmccully

You can use calculate to:

2. For the MAX date

Maybe something like this:

```CALCULATE(
MAX(Query1[METER]) - MIN(Query1[METER]),
Query1[METER_DATE] = MAX(Query1[METER_DATE])
)```
Frequent Visitor

## Re: Find a value associated with the latest date

I get this error when using your formula in a measure:

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Resolver II

## Re: Find a value associated with the latest date

Hey @cmccully

I saw your response last night when I got home from work. My apologies.

I should have formatted the filter argument of Calculate differently:

```Latest Date Max_Min =
CALCULATE(
MAX(Query1[Meter]) - MIN(Query1[Meter]),
FILTER(
Query1,
Query1[Meter_Date] = MAX(Query1[Meter_Date])
)
)```

Here is a screenshot of my test:

Super User IV

## Re: Find a value associated with the latest date

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Find a value associated with the latest date

Sorry for the delay in responding. I have posted a test file here:

Resolver II

## Re: Find a value associated with the latest date

@cmccully

Did you attempt with what I recommend Ed in my last post as that worked?
Frequent Visitor

## Re: Find a value associated with the latest date

Sorry for taking so long to respond. I created a measure using your new DAX code but am still not getting anything. I posted a link below to the PBIX test file I am using.

Here is what it looks like when I try to use the measure:

I want to get the mileage 106878 as that is the reading associated with the latest date.

Thank you!

Super User IV

## Re: Find a value associated with the latest date

Hi,

This measure works

`Measure = CALCULATE(SUM('Sheet 1'[METER]),LASTDATE('Sheet 1'[METER_DATE]))`

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Find a value associated with the latest date

Thank you, that worked!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors