Forum Discussion
PIVOT Table, MAX formula
Hello Team,
I am seeking some help and a solution to my issue. If it was already covered, kindly point it out to me. Thank you.
Example of the report on the link: File
Link with the screenshot: link
It is an inventory file that helps me when discussing my partner's orders/sellouts and inventory.
I see this report on a single Excel page, with the first column listing the products and subsequent columns providing details on inventory, sales, and orders. Our clients report their inventory and sales weekly, and we retrieve their orders from our ERP system. This layout makes it easy (for me) to analyze the data quickly and efficiently, facilitating better decision-making in our order planning.
Here's how I see the report:
| Product | Last Week inventory | Total Sales | Total Orders | |
| Product A | [Quantity] | [Quantity] | [Quantity] | [Quantity] |
| Product B | [Quantity] | [Quantity] | [Quantity] | [Quantity] |
My problem:
Inventory - the aggregation functions of the Pivot Tables logic is clear; it summarizes the total amount at the end of the row/line. But what I would like to see is the inventory from the last week, hence the reason I am using the MAX value for the string. But at the total line it shows the MAX number from the row, and not the latest value from the MAX date, which I need. I googled the "GETPIVOTDATA" formulas, but it is either not built for this, or I have a lack of knowledge (2nd option for sure). I can do a table with PIVOTBY, and SORTBY array formulas, but each time I refresh the DATA SHEET report (it is connected through Power Query to our system), it breaks the references, and I get #REF, #VALUE, and #CALC errors. So if you like challenges :), kindly have a look at the below. Thank you very much again for your time.
