The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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].
Solved! Go to Solution.
Hi,
This measure works
Measure = CALCULATE(SUM('Sheet 1'[METER]),LASTDATE('Sheet 1'[METER_DATE]))
Hope this helps.
Hi,
Share the link from where i can download your PBI file.
Sorry for the delay in responding. I have posted a test file here:
https://drive.google.com/file/d/14Vq9JOr5tWuilC4Z-rbM6BSnulIzJ8LU/view?usp=sharing
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]) )
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.
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.
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!
https://drive.google.com/file/d/14Vq9JOr5tWuilC4Z-rbM6BSnulIzJ8LU/view?usp=sharing
Hi,
This measure works
Measure = CALCULATE(SUM('Sheet 1'[METER]),LASTDATE('Sheet 1'[METER_DATE]))
Hope this helps.
Hello there, nice, it worked on my side as well. One question... why can't I substitute the lastdate() with min() or max()?
You are welcome. The LASTDATE() function returns a table of 1 row and 1 column whereas the MAX() function returns a single scalar value.
Thank you, that worked!
You are welcome.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
101 | |
74 | |
59 |
User | Count |
---|---|
260 | |
126 | |
115 | |
98 | |
87 |