Year to Date This Year vs YTD Last Year

[seethesun]

Hi,

I have a dashboard that is fed from a pivot table. I want to be able to have a running total for YTD, but last year (a second pivot table is fine). So as of today, I would want to see 1/1/15 through 4/1/15 (my dates are all the first of the month). I know I could just use the date as a filter on a pivot table and manually update each month, but would prefer it to be automatically updated.

Thanks,

David

[Richard Buttrey]

Hi,

Personally I'd bypass the Pivot Table and use the original data.
So assuming your dates are in column A and values in column B on Sheet1

In C1:

Please Login or Register  to view this content.

C2:

Please Login or Register  to view this content.

Then for your dashboard running total use a simple SUMIFS() function.

Please Login or Register  to view this content.

[seethesun]

Thanks Richard. I wanted to use a pivot table because I have 8 metrics I need to pull into the dashboard, four of which are calculated fields. The raw data is used by other pivot tables, with those same calculated fields, so I thought it would economical to go that route rather than reconfigure my raw data or making long formulas to perform the calculations.

The other reason is because the dashboard is dynamic. The metrics (that aren't YTD) can change based on the country choosen from a slicer. This YTD would also need to change based on that information.

[bhenlee]

try adding another column in your source data to show the Year.

=Year()

then adjust your pivot table

[seethesun]

Ok. I figured out how to modify your idea and turn it into a helper column. I used:

=IF(AND(G2>=DATE(YEAR($C$6)-1,1,1),G2<=DATE(YEAR($C$6)-1,MONTH($C$6),1)),"YTD","")

Where G2 is the date I am evaluating and C6 is the date it's evaluating it against, since my report goes through the first of last month. Now I will use the YTD column for a slicer.

[Richard Buttrey]

Thanks Richard. I wanted to use a pivot table because I have 8 metrics I need to pull into the dashboard, four of which are calculated fields. The raw data is used by other pivot tables, with those same calculated fields, so I thought it would economical to go that route rather than reconfigure my raw data or making long formulas to perform the calculations.

The other reason is because the dashboard is dynamic. The metrics (that aren't YTD) can change based on the country choosen from a slicer. This YTD would also need to change based on that information.

Nevertheless I still don't understand why you can't use the original data. After all the Pivot Table is at the end of the day an alternative view of the original data and it derives all its information from the data. If you have a calculated field in the PT then there'll be a formula that you can use to address the data and give you the same result as you'll get from a PT.

Just as you can use Slicers to filter totals from a PT you can use functions to filter that same information directly from the data.

The PT is a convenient way of presenting data but since the layout can change depending on how fields are moved around I don't think it's safe to base say dashboard information on a particular PT cell which may not be in the same position when a PT layout is changed.

[seethesun]

The pivot table, once set up, is behind the scenes and should not change. Yes, technically someone could get in there and mess with something, but they can just as easily do so with a formula in a cell.

It's easier and less messy than trying to construct a formula or formulas to get everything working.

Instead, I created one column in my raw data, created pivot table with the YTD, tied it to the slicer on the dashboard, and referenced the metrics in the dashboard.

I do appreciate the help and I definitely would have been at it longer if I didn't have your formulas to start off with.