• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Dashboard- linking data from previous year|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Dashboard- linking data from previous year|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsDashboard- linking data from previo…
sp_PrintTopic sp_TopicIcon
Dashboard- linking data from previous year
Avatar
Jen Smith
Member
Members
Level 0
Forum Posts: 20
Member Since:
January 13, 2021
sp_UserOfflineSmall Offline
1
January 27, 2021 - 3:47 am
sp_Permalink sp_Print

I needed to start over with a new dashboard for 2021 due to system capacity - over 400,000 rows of data.    I am utilizing power query with data model with 64-bit.  I copied and renamed my new dashboard for 2021 and changed the data source for the queries.   

My data source is from various files containing data for 2021 only.  One of my pivot tables show percent changes; therefore, my question is; Can I link the previous year so that my percent change table can continue throughout 2021 without running into the same problem with low memory.  

Thanks!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
January 27, 2021 - 5:14 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Jen Smith
Member
Members
Level 0
Forum Posts: 20
Member Since:
January 13, 2021
sp_UserOfflineSmall Offline
3
January 27, 2021 - 11:43 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
January 28, 2021 - 10:35 am
sp_Permalink sp_Print

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

Avatar
Jen Smith
Member
Members
Level 0
Forum Posts: 20
Member Since:
January 13, 2021
sp_UserOfflineSmall Offline
5
January 28, 2021 - 11:16 pm
sp_Permalink sp_Print

Thank you!

Avatar
Jen Smith
Member
Members
Level 0
Forum Posts: 20
Member Since:
January 13, 2021
sp_UserOfflineSmall Offline
6
January 29, 2021 - 2:30 pm
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
January 30, 2021 - 1:56 pm
sp_Permalink sp_Print

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

Avatar
Jen Smith
Member
Members
Level 0
Forum Posts: 20
Member Since:
January 13, 2021
sp_UserOfflineSmall Offline
8
February 2, 2021 - 7:13 am
sp_Permalink sp_Print

Thanks so much!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27294

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.