CODEWITHSUNDEEP
excelpivot-table
Gangula
Gangula

Reputation: 7362

Calculate difference from previous date in Excel pivot table

I have a table like below. In this table, the "model" column contains file type information.

We log the latest updated date of any file Type(AFP/Arch/ etc..) along with the date of the day that we are inputting these values.

For example, if I'm inputting the values today and I found that the file AFP is updated yesterday. The row would be like

|File type|   |Date of Input|    |updated date|
|  AFP    |   |  2018.05.09 |    | 2018.05.08 |

Date update table

Using this I would like to create a pivot table that shows me the Difference of the column "Update" from the Previous "Date". The desired values in the above example would be "1"(the difference between today & yesterday). But using the show value as "Difference from" option doesn't give me proper values with dates.

Please, find the date below.

excel image

| Model         | Date       | Update     |   |   |
|---------------|------------|------------|---|---|
| AFP           | 2018.03.05 | 2018.02.26 |   |   |
| AFP           | 2018.03.07 | 2018.02.26 |   |   |
| AFP           | 2018.03.09 | 2018.02.26 |   |   |
| AFP           | 2018.03.14 | 2018.02.26 |   |   |
| AFP_FP        | 2018.03.07 | 2018.03.06 |   |   |
| AFP_FP        | 2018.03.09 | 2018.03.09 |   |   |
| AFP_FP        | 2018.03.14 | 2018.03.09 |   |   |
| Arch          | 2018.03.05 | 2018.02.22 |   |   |
| Arch          | 2018.03.07 | 2018.02.22 |   |   |
| Arch          | 2018.03.09 | 2018.02.22 |   |   |
| Arch          | 2018.03.14 | 2018.03.13 |   |   |
| EL            | 2018.03.05 | 2018.03.01 |   |   |
| EL            | 2018.03.07 | 2018.03.07 |   |   |
| EL            | 2018.03.09 | 2018.03.09 |   |   |
| EL            | 2018.03.14 | 2018.03.09 |   |   |
| PO HD         | 2018.03.05 | 2018.03.02 |   |   |
| PO HD         | 2018.03.07 | 2018.03.07 |   |   |
| PO HD         | 2018.03.09 | 2018.03.09 |   |   |
| PO HD         | 2018.03.14 | 2018.03.14 |   |   |
| PO HP         | 2018.03.05 | 2018.03.02 |   |   |
| PO HP         | 2018.03.07 | 2018.03.07 |   |   |
| PO HP         | 2018.03.09 | 2018.03.09 |   |   |
| PO HP         | 2018.03.14 | 2018.03.14 |   |   |
| PO PL         | 2018.03.05 | 2018.03.02 |   |   |
| PO PL         | 2018.03.07 | 2018.03.07 |   |   |
| PO PL         | 2018.03.09 | 2018.03.09 |   |   |
| PO PL         | 2018.03.14 | 2018.03.14 |   |   |
| RCP           | 2018.03.05 | 2018.02.26 |   |   |
| RCP           | 2018.03.07 | 2018.02.26 |   |   |
| RCP           | 2018.03.09 | 2018.02.26 |   |   |
| RCP           | 2018.03.14 | 2018.02.26 |   |   |
| SPW()         | 2018.03.05 | 2018.02.26 |   |   |
| SPW()         | 2018.03.07 | 2018.02.26 |   |   |
| SPW()         | 2018.03.09 | 2018.02.26 |   |   |
| SPW()         | 2018.03.14 | 2018.02.26 |   |   |
| STR           | 2018.03.05 | 2018.02.22 |   |   |
| STR           | 2018.03.07 | 2018.02.22 |   |   |
| STR           | 2018.03.09 | 2018.02.22 |   |   |
| STR           | 2018.03.14 | 2018.02.22 |   |   |
| STR - Patriot | 2018.03.09 | 2018.03.07 |   |   |
| STR - Patriot | 2018.03.14 | 2018.03.07 |   |   |
| TD PL         | 2018.03.05 | 2017.07.11 |   |   |
| TD PL         | 2018.03.07 | 2017.07.11 |   |   |
| TD PL         | 2018.03.09 | 2017.07.11 |   |   |
| TD PL         | 2018.03.14 | 2017.07.11 |   |   |

Upvotes: 0

Views: 9072

Answers (1)

Hakan ERDOGAN
Hakan ERDOGAN

Reputation: 1210

After dragging Model pill to rows and Date Pill to Columns (which seems that you have done this already), you should drag the Update to values, summarize it by Sum and Show Value as Difference From with base fields Date and Base item previous

The final screenshot is as follows. Only that your desired value for 14.03.2018 X Arch is 21, my calculation is 19:

enter image description here

Upvotes: 2

Related Questions