April 28, 2020
MY company runs its financial year from April to March. I am building a dashboard for income statements and need to compare year on year and month on month.
Can you please assist in how I set up my data so that power pivot/excel power query allows me to analyse the months linked to their fiscal year FY and also, so that it knows that when I am comparing April to April that I am comparing prior year to current year.
July 16, 2010
You need to add the fiscal period fields/columns (for quarters & years) to your date/calendar table. It's most efficient if you use Power Query to do this before loading it into Power Pivot.
Once you have these fields in Power Pivot, you can use them in your PivotTables.
Here is a tutorial explaining how to convert dates into fiscal periods using Excel formulas. You can convert them to their Power Query equivalents for use in Power Query.
I hope that points you in the right direction.