I've watched the videos and searched the forums, but I'm still a little confused on the best way to calculate a fiscal year, fiscal qtr, and fiscal month in my calendar table (when the fiscal year etc. is different from the standard calendar year); and if it's best to do so in Power Pivot or Power Query. Can anyone provide an example? Thanks!
Hi Amber,
It's more efficient to create your date table in Power Query as this removes the need to have calculated columns running in Power Pivot.
Create another table that maps the month number into the fiscal month and quarter e.g. if your fiscal year starts July 1 then it would look like this:
| Month Number | Fiscal Month | Fiscal Quarter |
| 1 | 7 | Q3 |
| 2 | 8 | Q3 |
| 3 | 9 | Q3 |
| 4 | 10 | Q4 |
| 5 | 11 | Q4 |
| 6 | 12 | Q4 |
| 7 | 1 | Q1 |
| 8 | 2 | Q1 |
| 9 | 3 | Q1 |
| 10 | 4 | Q2 |
| 11 | 5 | Q2 |
| 12 | 6 | Q2 |
Add a column to your date table that extracts the Month number from the date column. Then merge the two tables based on the Month Number columns to bring in the fiscal month and quarter columns.
For the year; add a column to your date table that extracts the year from the date column. Add a custom column with the following formula (edit for your own column names):
=if [Fiscal Month] >= 7 then [Year] else [Year]-1
Mynda
