• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Pivot Table - KPIs across years|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Pivot Table - KPIs across years|Power Pivot|Excel Forum|My Online Training Hub

vba course banner

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 ForumPower PivotPivot Table - KPIs across years
sp_PrintTopic sp_TopicIcon
Pivot Table - KPIs across years
Avatar
Karen Birkett

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 11, 2022
sp_UserOfflineSmall Offline
1
May 11, 2022 - 1:57 am
sp_Permalink sp_Print

Our KPI year runs from 16 December of one year to 15 December of the following. My data starts on 16 December 2017.
I therefore have 3 complete years worth of complete data (2017-2018, 2018-2019, 2019-2020) in separate files which will no longer need to be updated and 2 files (2020-2021, 2021-2022) which will get updated on the 15th of every month.

Before now every KPI has been manually calculated and I am now responsible for trying to automating this.

I think I have been able to use Power Query to combine all this data together but I am struggling using Powerpivot to display the KPIs correctly because of the crossover of years. 

 

I also need to make calculations from some of the results in the pivot tables and am seeking some recommendations on how best to do this. The calculations would be  in Column D are and are Column C/Column B.

powerpivot.jpgImage Enlarger

 

Hope you can help.

sp_PlupAttachments Attachments
  • sp_PlupImage powerpivot.jpg (23 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 11, 2022 - 12:23 pm
sp_Permalink sp_Print

Hi Karen,

Welcome to our forum!

It sounds like you need a new column in your data for the financial year that classifies each date into the correct year, which you'll then use in your PivotTable.

For your calculation in column D, you'll need to write a DAX measure using the DIVIDE function. 

I hope that points you in the right direction. If you'd like to get your Power Pivot and DAX skills up to speed, please consider my course.

Mynda

Avatar
Karen Birkett

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 11, 2022
sp_UserOfflineSmall Offline
3
May 11, 2022 - 6:12 pm
sp_Permalink sp_Print

Thanks for the welcome and response Mynda

Is the best way to add the column via the load and transform method?  Or do I add it manually to the raw data.  The raw data is extracted from some clinic software every month so there is no way to add this to the database - only the raw extracted data....every month.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
May 11, 2022 - 10:22 pm
sp_Permalink sp_Print

Technically you should have a date dimension table and in that table you would have your financial year column. You can create this in Power Query or Power Pivot, but creating it in Power Query is slightly more efficient for Excel. If you only have one table of data and there's not hundreds of thousands of rows, then you could add the financial year column to that table in Power Query.

Avatar
Karen Birkett

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
May 11, 2022
sp_UserOfflineSmall Offline
5
May 13, 2022 - 2:26 am
sp_Permalink sp_Print

Thanks! I watched your video Convert Dates to Fiscal Periods with Power Query - Better than Formulas! and it helped me do what I needed!

Such a wealth of information. Thanks so much.

The following users say thank you to Karen Birkett for this useful post:

Mynda Treacy
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sherry Fox, Russell Artho, Barry Tobin
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27793

 

Member Stats:
Guest Posters: 49
Members: 32324
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.