Formula

[Ing_Fred_Fiifi_]

 Please I want the last number that will be entered in the Customer trend which is the red 🔴 one, should also appear at the previous month column which is the green one ... Will be grateful to get help. Thanks

[Markus_Malessa · Accepted solution]

If you need more than just the reading from the customer trend I would suggest setting a virtual column as REF first that points to the entire row, then set up additional virtual columns to the last row [Date] and [Reading] for example. So the formula for the latest row would be:

MAXROW("Customer Trend", "Date", [Meter No] = [_THISROW].[Meter No])

As already pointed out, if you have multiple readings on the same date this also will not work. Furthermore, if you are trying to show the previous meter reading inside a form that you are using to record the current meter reading then you may also need to add a filter for [Date] < [_THISROW].[Date] to make sure the data does not point to the current pending entry.

[WillowMobileSys]

In your example you show 2 entries both on 12/18/2023, how does the app know which one was truly the last one entered?  Maybe this is not a good use case - i.e. you wouldn't have multiple entries on the same date?  If there can be multiple entries, then you should use a DateTime column to more accurately capture the last reading.

Regardless of how you resolve the ambiguity above, the expression Previous Month reading would need to be something similar to:

SELECT(Customer Trend[Reading], MONTH(Date) = (MONTH(TODAY()) - 1))

 Note:  This version of the expression assumes there will NOT be multiple entries per month.  If there are, then an adjustment will be needed to choose the MAX DateTime (if that is how you handle it).

[Ing_Fred_Fiifi_]

PLS CHECK THE ERROR

[Markus_Malessa · Accepted solution]

If you need more than just the reading from the customer trend I would suggest setting a virtual column as REF first that points to the entire row, then set up additional virtual columns to the last row [Date] and [Reading] for example. So the formula for the latest row would be:

MAXROW("Customer Trend", "Date", [Meter No] = [_THISROW].[Meter No])

As already pointed out, if you have multiple readings on the same date this also will not work. Furthermore, if you are trying to show the previous meter reading inside a form that you are using to record the current meter reading then you may also need to add a filter for [Date] < [_THISROW].[Date] to make sure the data does not point to the current pending entry.

[Ing_Fred_Fiifi_]

Thank so much but how do I add the filter.. Kindly get me the formular

[Markus_Malessa · Accepted solution]

Not entirely sure what your follow up question is. You would set up a virtual column, name it PreviousRow and make sure you set it as REF type pointing to table "Customer Trend"

MAXROW("Customer Trend", "Date", AND([Meter No] = [_THISROW].[Meter No], [Date] < [_THISROW].[Date]))

Then set up a virtual column for PriorReadingDate

[PreviousRow].[Date]

And another virtual column PriorReading

[PreviousRow].[Reading]

 Then in your detail view or form view insert [PriorReadingDate] and/or [PriorReading] as necessary.

[Ing_Fred_Fiifi_]

Thanks so much it work 🥰🥰🥰