PIVOT Table, MAX formula

Copper Contributor

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 inventoryTotal SalesTotal 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.

4 Replies

Hello @Vitalie_C 

 

With Power Pivot (assuming you run Excel / Windows):

sample.png

 

A few intermediate Measures are not visible in the Pivot fields but are in the Model

Hi @L z., i see that i works as requested, thank you ! Can you help me with the steps too?

Thank you

Hi @Vitalie_C 

 

Can you help me with the steps too?

What do you exactly mean/wants (currently away - don't expect timely reply)

@Vitalie_C 

At the bottom of each reply you get on this website there's a Mark as response link. Clicking it helps people who Search - Thanks