cancel
Showing results for
Did you mean:
Highlighted
Helper I

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

Thank you for any help you can provide.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V

## 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
Highlighted
Resolver II

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

Hey @cmccully

You can use calculate to:

1. Get the MAX reading

2. For the MAX date

Maybe something like this:

```CALCULATE(
MAX(Query1[METER]) - MIN(Query1[METER]),
Query1[METER_DATE] = MAX(Query1[METER_DATE])
)```
Highlighted
Helper I

## 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.

Highlighted
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:

Click HERE to access my pbix file used for testing.

Highlighted
Super User V

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

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Helper I

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

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

Highlighted
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?
Highlighted
Helper I

## 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!

Highlighted
Super User V

## 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
Highlighted
Helper I

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

Thank you, that worked!

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors