Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm looking for a function that will allow me to calculate the number of miles a car has put on since the last time it was serviced. Basically the function would filter on the "Car" column for the matching car and calculate the difference between the current "Total Miles" column and the most recent previous milage reported in the table.
Example below, the "Miles since last report" is the column I would like to calculated automatically.
Car | Date | Total Miles | Miles since last report |
Subaru Legacy | 11/1/2018 | 17,353 | 0 |
Subaru Legacy | 11/25/2018 | 17,851 | 498 |
Subaru Legacy | 1/25/2019 | 19,515 | 1,664 |
Subaru Forester | 11/1/2018 | 35,252 | 0 |
Subaru Forester | 11/25/2018 | 36,363 | 1,111 |
Subaru Forester | 1/25/2019 | 38,232 | 1,869 |
Subaru Forester | 2/28/2019 | 39,968 | 1,736 |
Honda Civic | 11/1/2018 | 93,526 | 0 |
Honda Civic | 11/25/2018 | 93,888 | 362 |
Honda Civic | 1/25/2019 | 95,025 | 1,137 |
Honda Civic | 3/25/2019 | 98,252 | 3,227 |
Honda Civic | 4/28/2019 | 100,025 | 1,773 |
Solved! Go to Solution.
@prj102 you can add a column using following expression
# of Miles = VAR __prevMiles = CALCULATE( LASTNONBLANK( Table3[Total Miles],1 ), ALLEXCEPT( Table3, Table3[Car] ), Table3[Date] <EARLIER(Table3[Date])) RETURN IF(NOT ISBLANK( __prevMiles ), Table3[Total Miles] - __prevMiles )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Would you want this to be a calculated column formula or a measure?
@prj102 you can add a column using following expression
# of Miles = VAR __prevMiles = CALCULATE( LASTNONBLANK( Table3[Total Miles],1 ), ALLEXCEPT( Table3, Table3[Car] ), Table3[Date] <EARLIER(Table3[Date])) RETURN IF(NOT ISBLANK( __prevMiles ), Table3[Total Miles] - __prevMiles )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
This seems to work as I expected. Thank you for the solution. I'll reach back out if I find any issues.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |