This blog post accompanies our recent webinar, Power Pivot: Management reporting. In this post I will go through all the questions we received during the live session – including the ones we didn’t have time for on the day – and answer every single one.
Does the date table auto-update if dates outside of the original range are added?
No, unfortunately Calendar tables don’t auto-update, and this can cause issues when new data goes beyond the original date range. You can update the range by going to the Calendar table in the Power Pivot window and using Design => Date Table => Update Range. There’s no particular cost to including several future years at the point of creation just to future-proof the workbook.
How would you compare this method vs combining the data in Power Query by the merge function to create one data table which can then be used as the source for our Pivot?
Power Pivot connections are much simpler to make and maintain than PQ ones, requiring only a click and drag. They also support larger interconnected networks of tables much more straightforwardly.
Is this still only available on Windows, and not on Mac versions of Excel?
Power Query was finally rolled out to Excel for Mac in 2020, but as of yet PP is still not available for the platform.
What's the best way to arrange the dates if the financial year isn't the same as the calendar year?
For the most part, you can just use timeline slicers etc. to select the date period that you want to analyse. Some functions that refer to a calendar year, like the DATESYTD function which filters dates to show year-to-date, have an input that allow you to set a non-31st December year end. You could also add a table with year end dates that mapped to your financial years and connect that into your data.
How do you add a variance column between actual and budget?
You can create a secondary Measure that refers to others very easily – so for example:
Var to Budget:=[Current year measure] – [Budget measure]
If you wanted to do the same analysis each month, would you set the pivot tables up and then paste your new data into the data tables?
I assume next month for example you can update your data set and hit refresh and the report would update?
Yes, you could just add new data and then use Data => Refresh.
How would this work with monthly TB's as opposed to line level transaction data?
It would all work fine, although of course having monthly input data would put a lower bound on how deep you could break down your date data.
Note that for the Calendar table to work every item has to be labelled with an actual date, so if your monthly data outputs e.g. “March 2022” you would need to adjust that to an arbitrary date such as “01/03/2022”.
What are the main benefits of Power Pivot over using Power BI as they seem to use very similar functionality?
They are quite similar, yes, and both essentially use Power Query as the back-end for manipulating data. Power BI has more visualisation and presentation options than Power Pivot, but is less familiar to Excel users. It also isn’t part of the standard Office suite and so licenses cost extra.
What's the functionality of Power Pivot like when linking to an external database as opposed to copy and pasting with new data?
PP can only work with what is in Excel’s Data Model. It can help you add regular Tables in Excel to that model, but for anything based on external data, you need to use Power Query to access that data. One of the output options from PQ is to export to the Data Model, which will then make the output data available to be connected and used in PP.
How do you create % change from one period to the next and how can you add the % that an expense might have relative to sales?
You can make a Measure using the DIVIDE function like this:
=DIVIDE(numerator Measure, denominator Measure, BLANK())
This uses the BLANK() function to display a blank cell if the division fails (e.g. if dividing by zero).
Can you use measures to calculate monthly data if your source data is loaded into Power Pivot on a cumulative basis?
It depends on what you mean by “cumulative”. If your data is a long list of daily transactions, that’s no problem. If you have only got YTD amounts for each month, you could create a Measure of some kind to determine the monthly amounts, but it would depend on the exact data format.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
