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.
Hope you can help.
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
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.
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.
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.
