Pivot tables in excel - showing last values

0
votes

I am having an issue with showing last value.

I have a source table, where is every single day (once) and for each day I have for item 1 total amount in stock.

enter image description here

I put it in pivot table. Column for total item in stock, for each day in month July for example, shows me balance for every day. When I group the month, it shows cumulative amount - which is wrong. I need to show last value.

Therefore I searched for a solution, I found on webpage ExcelJet this.

But when I tried it, for some reason, the date 31/07/2020 shows value 0 on top first, and on top second place the correct value for the last day of month.

enter image description here

Does anyone know why that happens? In source data there is 31/7/2020 with only 9.546.

2
excelpivot-table

2 Answers

0
votes

You are applying the filter in the wrong field. You must apply it to your AMOUNT field, and you are applying it to your ITEM field (the field that holds the value VEL).

This is happening because you are working in the compact view of Pivot Table, and I think it's easier to work in tabular design.

Design the layout and format of a PivotTable

My Pivot Table is like this:

enter image description here

As you can see, ITEM and AMOUNT field are in the rows section. And DATE field in the Values section, set to MAX DATE and renamed as LATEST.

The filter is applied in the field AMOUNT.

0
votes

I know what you mean, but actually the filter is applied on the correct field/row. See the prtscrn.

enter image description here

enter image description here

For the references, I attached the source table too.

enter image description here

btw, if I tried filtered out the column Note it still doesn't work properly