Sum all values for the most recent date

1

I have a giant table of sales data, and I want to create another table that displays just the sum for the most recent sales. The problem is there are multiple sales for the most recent date, and the formula I’m using just gives me the last entry for the most recent date. Here’s the formula I’m using:

([Master Payment Log].Filter(Customer = thisRow).sort(False,[Master Payment Log].[Payment Date])).First().[Payment Amount].Sum()

What can I do?

2

HI @Dana_Barrow,

It always helps if you can share a copy of the doc itself, or if it has sensitive data, if you create another doc with the same structure and fake data. That makes it a lot easier for folks to assist as we can dive in and have a look.

But in this case, looking at your formula, the .First() will be the reason you are only returning a single record. The First formula returns the first record of a data set.

You will also need a date comparison in the Filter.

Try something like this -

[Master Payment Log].Filter(
Customer = thisRow
AND [Payment Date] = [Master Payment Log].Filter(Customer = thisRow).[Payment Date].Max()
).[Payment Amount].Sum()