Having issues doing a Summarize tool for a unique situation

Question

I essentially have two datasets that I am trying to extract information from one and append to the other. For example, In my 'Sales' report, I have a record that looks like this:

ProductDateAmountUnitsA 20

I need to population the date and the ending units for the month using my transactional level data set that looks like this:

ProductDateAmountUnitsA07/01/2024510A07/22/2024515A07/24/2024520A07/28/2024525

basically a transaction report, however, the Units column is a running total. I'd like to be be able to summarize this data set into the following:

ProductDateAmountUnitsA07/28/20242025

grouping by product for the total amount of sales with the ending units balance with the last date populated.

Any ideas how to achieve?

thank you in advance,

alexnajm · Accepted Answer

Instead of summing on Units, do a Max function in your Summarize tool!

OTrieger · Accepted Answer

Hi @CDIns

You will need to set the Summarize Tool As follow:
Group by Product
Last Date
Sum Amount
Max Unit as mentioned by @alexnajm