Hi Jen,

You might be able to load a table of summarised values for 2020 into a Power Pivot model along with your current year's data that will allow you to perform the comparison. Hard to say much more without knowing your model.

You haven't said if you're using Power Pivot, but if you're not, then that will get around the system capacity issue as Power Pivot can handle 10's of millions of rows of data inside of Excel.

Mynda

Hi Mynda,

I am using Power Pivot.  I was looking to show the percentage of change month by month on a continuous basis. Would you recommend just starting over instead and just having a separate 2020 dashboard for reference? This isn’t financial information although I wanted to just keep going.     

When you suggest creating a new pivot table of summarized values from 2020, Does this mean that I would just use December 2020 values and pick up from there somehow?

Thank you

Hi Jen,

64-bit Power Pivot should easily be able to handle 400k rows of data. I wonder what else is in the workbook that's causing performance issues. e.g. is the source data also in a worksheet (possibly with loads of formulas) that is then being loaded into Power Pivot? If so, it would be better to move the source data to an external file and use Power Query to bring it into a separate file containing the Power Pivot model. 

I didn't suggest creating a new PivotTable, rather a separate 'Table' inside your Power Pivot model that contained the values you need for the prior year at the level of granularity you'll need for your final PivotTable report. e.g. if you need it at month level, then add up the daily values to get month totals and load them in as one value for each month (using the first or last of each month for the date). Obviously you'll have other levels of detail you'll want those values broken down by, but I'm not familiar with your model to suggest those.

I hope that makes sense.

Mynda

Thank you!

Hi Mynda,

Thank you for your prompt responses and all of your feedback!  I appreciate it.   

My data sources are from external files that I pull into Power Query. One year of Information is well over 400,000 rows. This is why I decided to use Power Pivot in the first place. I began getting out of memory messages in October 2020.  After doubling my memory from 8 GB to 16 GB it got much better, although sometimes it’s still difficult to open a query, even those with only hundreds of rows.  I also have a lot of power query steps applied perhaps that slows it down too.

It’s not really essential that 2 years of data show on one dashboard: It’s just my preference because the slicers make it so nice and easy to navigate. And it’s nice to just keep adding to the same external data source Tables and to just click Refresh to update my dashboard with the new information.  Although the Tables do get very long, after a year and some of them do contain formulas, but not all of them.

To avoid running into these memory issues again, I figured starting over for the new year was the answer. I copied, and renamed my files to set up for the new year so that I don’t have to do my dashboard over. I also changed my data source in my queries pointing to the new files thanks to your instructions!  In order to keep the month to month percentage change going for one of my Tables, I’ve decided to only include Dec 2020 data with the 2021 data, so that for January we can still see the difference from last month (Dec 2020). This was my first concern being able to see the percentage change on a continuous basis. 

Now I’m trying to figure out if from my original file with my queries, pivot tables and dashboard - If I could pull the 2021 data (from external files which are separate from 2020) in with Power Query and load them to the data model to achieve this result, to have both years together all on one dashboard. Can this be done, or does the data for 2021 need to continue being added to the original data source?   Since, we can change the data source in our queries, I thought maybe this could work, not to mention how the data model (Power Pivot) should be able to handle millions of rows. 

But then I would have additional queries for the other Year in my current file with my existing pivot tables and dashboard.  Not sure about this . Should I just start over?

Thanks again.

Hi Jen,

You can get the 2021 data from separate files using Power Query, but you would then need to append that to the 2020 table of data that's loaded to Power Pivot, so that all of the 'facts' are in a single table.

If you're getting data from 'files in a folder' then you'd simply add the 2021 files into the same folder and Power Query would append them all together into one table.

Hope that makes sense.

Mynda

Thanks so much!