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

 

2019-08-23_13-20-19.jpg

 

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
Super User IV
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
ccapowerbi Resolver II
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])
)
cmccully
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.

ccapowerbi Resolver II
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:

Capture.PNG

 

 

 

 

 

 

 

 

Click HERE to access my pbix file used for testing.

Super User IV
Super User IV

Re: Find a value associated with the latest date

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
cmccully
Frequent Visitor

Re: Find a value associated with the latest date

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

https://drive.google.com/file/d/14Vq9JOr5tWuilC4Z-rbM6BSnulIzJ8LU/view?usp=sharing

 

 

ccapowerbi Resolver II
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?
cmccully
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:

 

Power BI Screen Shot 1.jpg

 

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

Thank you!

 

https://drive.google.com/file/d/14Vq9JOr5tWuilC4Z-rbM6BSnulIzJ8LU/view?usp=sharing

Super User IV
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

cmccully
Frequent Visitor

Re: Find a value associated with the latest date

Thank you, that worked!

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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