How can I filter by the last row added?

[vadebo]

Hi!

I am building a weight loss tracker and I have a table where I input the different data (date, weight, body size…). In the dashboard I want to have a summary with the last data entered, for example, Current Weight = X, Current Waist = X…

However, I can’t find a way to filter to show the data of the last row added.

If anyone can help me, I would be very grateful.

Thank you!

[Piet_Strydom]

There is a formula max(), which you can apply against the property created on of each row. That will return the most recent record created.

[Pch]

Hi @vadebo and Welcome to the Community !

As @Piet_Strydom mentioned, you could use something like :

Table.Filter([Row - Created] = [Row - Created].Max()).First()

(See Formula 1 in the sample below )

And what it does is take the table Table () and look for the row where the CurrentValue.[Row - Created] is equal to CurrentValue.[Row - Created].Max() (with is the most recent row created in the table).
And because Filter() always outputs a list of rows, .First() is there to extract the actual row from the list .

Or you can use MaxBy()
(which exists to simplify these kind of use cases (i.e.: avoiding the use of Filter() to pin-point a maximum value within a list))

Table.MaxBy([Row - Created])

Note that [Row - Created] is also, in fact, CurrentValue.[Row - Created].
(See Formula 2 in the sample below )

I hope this helps

[vadebo]

Thank you so much @Pch and @Piet_Strydom for your answer. I didn’t know the formula Max().

However, it’s not exactly that I wanted. I want to build something like this picture.

[Pch]

Hi again @vadebo !

From either of the formulas I previously gave you, which returns the last row created in your table (i.e.: the most recent entry in the table) you should be able to get all those values by dereferencing them from the row returned by the formula.

So, one of the formulas was:

Table.MaxBy([Row - Created])

And it actually outputs the most recently created row in the table Table (based on the creation date/time row property) which in the sample doc I shared earlier is the row @Test 9.

And from that row, we can dereference the value stored in the field [Row - Created]

Table.MaxBy([Row - Created]).[Row - Created]

… which would return 2023-03-21, 13:14:06 (still from the sample I shared earlier)

Same thing should be possible within your doc …

If you take a look at the sample below and especially at the green canvas formulas, you’ll see that to get the most recent weight in the callout, I used :

Format(
  "{1} kg",
  Table.MaxBy([Entry Date]).[Weight (kg)]
)

and used this one, for the most recent hip size per the [Entry Date] field :

Format(
  "{1} cm",
  Table.Filter([Entry Date] = [Entry Date].Max()).First().[Hip (cm)]
)

Both formulas (Filter(...).First() and MaxBy(...)) in this case return a row (the reference of a row, to be precise) from a table first and from that row, we then get (dereference) the values stored in the fields [Weight (kg)] and [Hip (cm)] … and those values can then be used within Format() or Concatenate() to be displayed on the canvas.
(The corresponding row for those values is highlighted in the table)

Same principle should be applied for the other measures you wish to display in your dashboard

I hope this helps

[vadebo]

Fantastic!

Thank you so much.

I still have a lot to learn

[Pch]

No problem @vadebo !

@vadebo

I still have a lot to learn

No worries, it’ll will come !

If you encounter issues or have questions, don’t hesitate to come back and ask away .
We’re all here to help !