Pivot Table Most Current Data Excel

0
votes

I have a work pivot table setup to measure skills of employees. Now the process as it currently stands, is that every few months an employee will go and update one to three of skills.

The pivot table then looks like this:

Skills            6/1/2016  12/31/2016  4/3/2017    9/1/2016    Grand Total
Action Oriented         3       4       3           2               4

So currently the Grand Total function, takes the max. I was wondering if there is anyway to set up a calculated field in the pivot table, that will take values as of the last date. And if certain skills arn't updated it will just take the the last value thats already in there. I.e. if skills: action oriented, creative, and management abilities all got updated on 9/1/2016. But the last time client focus was updated was in June, then this calculation field will take the june value for client focus.

1
excelpivot-table
Why is 9/1/216 the last one when 4/3/2017 comes after? – Alexis Olson
Also, what does the pivot table's source data look like? – Alexis Olson
Hey Alexis, its just a typo. Pivot Source is vertical: so Name of person, skill ,rating, date. – MattO
This question is quite a bit harder than I initially suspected, but I have a work around that uses Power Query if you're interested. – Alexis Olson

1 Answers

0
votes

If your data is stored in the Excel data model (Power Pivot) you can use DAX-functions to check for the latest date values only. This provides a lot more feature than the regular pivot table and is quite easy to set up (just click "import from table" if you data is already stored in Excel).

I don't know what the source table looks like but you can either create a calculated column to check for the MAX([date] / LASTDATE[date] or create a measure / calculated field to count or sum up values with the last date only as in =CALCULATE(SUM([you value column]),LASTDATE([your date column]).

Check this for more info.