I m using similar function for my dashboard [ First value and last value of the day]. and the data i m expecting only one record per month but receiving more than one record
please see my expression used in calculated field.
please find my group used for the table
But the table list one vin number record continuously multiple time
Hi @srinivasan_ab ,
what to you want to obtain as final result?
In this case the Month-VIN No are replicated cause there are the other fields which have different values, so it cannot become a single row (if I understood correctly your need).
Let us know!
Andrea
My Expectation is one entry per vin number per month. My source data contains one record per day for one vin number.
Month – VIN No – Start Odo-- End Odo – Total Distance
Jun 2025 | MD9xxxxx | 9450 | 10442 | 992
July 2025 | MD9xxxxx | 10442 | 10972 | 530
Hi @srinivasan_ab ,
as you see, from your screenshot, you have fields in the Group By section which have different values, so it cannot become a single row as output.
Try removing start_odometer and end_odometer from the group by section if you do not need them, or put them in the Value section if you need to aggregate them in some way.
Let me know if this solves the issue. 
Andrea
Hi @andrepgn if we remove start_odometer and end_odometer value from group then my calculated field value wont work, it throws error.
Can you show me how you generated the other fields?
From my understanding StartOdometerMonth and EndOdometerMonth are both calculated fields, right?
Oh sorry, you’re right
So, if I understood correctly what you want to do, I think you can try to obtain it in this way:
StartOdometer_NEW:minOver(min({start_odometer}), [VIN], POST_AGG_FILTER)
EndOdometer_NEW:maxOver(max({end_odometer}), [VIN], POST_AGG_FILTER)
Distance:{EndOdometer_NEW}-{StartOdometer_NEW}
Now selecting these fields, you should obtain something like this:
Let me know if this is the result you were looking for!
Andrea
This is again providing me the Min(Start_Odometer) & Max(Start_Odometer) which is not correct when ever there is a reset of odometer value in between the month
I missed the part where the odometer can be reset during the month, sorry.
Maybe I’ve found a workaround, but it has to be checked on your real data, so let me know.
The idea is filtering the table to show only one row for each month/vin combination, so:
try creating a calculated field which gives you if a specific row is the first row for the combination vin/month:
ifelse(rank([{report_date} ASC], [VIN, truncDate('MM', {report_date})]) = 1, 1, 0)
In this case, for each combination, you will have a row with rank value 1, all the other ones as 0. Now you just have to filter the visual for rank = 1 and you will have one row per combination.
You don’t even have to add the field in the table.
Give this a try
You then hide the two start and end odometer field
Hi @srinivasan_ab,
It’s been awhile since we last heard from you, did you have any additional questions or did the last solution help with your case?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you
If we provide rank then i will have two entry one for start odometer and another for end odometer which is also not expected
Hi @srinivasan_ab,
Apologies from the community on missing your last response, please make sure to tag the user you’re responding to so that they get notified! If you’re still working at this, could you please provide a screenshot of what was produced from the last screenshot so that we can see how the ranking is altering the visual? Or if you were able to find a work around, feel free to share your solution here for the rest of the community.
Thank you
Hi @srinivasan_ab,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions regarding your initial post, feel free to create a new post in the community and link this discussion for relevant information.
Thank you
