Sorting months according to a fiscal year rather than a calendar year can be a bit tricky, especially when your fiscal year doesn’t start in January. In many businesses, the fiscal year starts in a different month, such as August or July, which means you need to reorder the months to align with your financial reporting.
In this guide, I’ll show you how to sort your months according to the fiscal year in Power BI based on a real scenario where the fiscal year starts in August and ends in July. Initially, there were issues with sorting, but after a few adjustments, I managed to solve it.
Problem Scenario:
I needed to sort fiscal months in a Power BI matrix from 1 (August) to 12 (July). Initially, I created a conditional column to specify the financial month order, but the sort order wasn’t displayed correctly in the report. After investigating, I found that the problem was related to the data type of the fiscal month order, which was fixed by changing it to a whole number.
Step-by-Step Guide to Sorting Months by Fiscal Year
Step 1: Create a Conditional Column for Fiscal Month Order
The first step is to create a conditional column that will define the order of the months based on your fiscal year. Here’s how to do it:
- Open Power BI and load your dataset.
- Go to the Power Query Editor and click on the Add Column tab.
- Click on Conditional Column to define a new column.
- Create a condition that assigns a number to each month according to your fiscal year. For example, if your fiscal year starts in August, set August to 1, September to 2, and continue until July, which will be set to 12.Example conditions:
- If Month = “August”, then 1
- If Month = “September”, then 2
- If Month = “October”, then 3
- Continue this process until July = 12.
Step 2: Use the New Fiscal Month Column in Your Matrix
Once you’ve created the fiscal month order column, it’s time to apply it in your matrix visualization.
- Select the matrix or table where you want the months to be displayed.
- Ensure that you’ve added the column representing the month (e.g., “Month Name”) to the matrix visualization.
Step 3: Sort the Fiscal Month by the New Order
To make sure your months are sorted correctly according to the fiscal year:
- In the Table View, select the Month Name column.
- Go to the Sorting and click on Sort by Column.
- From the dropdown, select the newly created Fiscal Month Order column. This will tell Power BI to sort the months based on the fiscal order you defined earlier.
Step 4: Correct Data Type for the Fiscal Month Order
If after performing the steps above you’re still not getting the correct sorting, the issue might be with the data type of the Fiscal Month Order column.
- Go to the Data View in Power BI.
- Select the Fiscal Month Order column.
- In the Column Tools under the Properties section, change the data type to Whole Number. By default, Power BI might have set it as a Text data type, which can prevent proper sorting.
Step 5: Verify the Results
After adjusting the data type:
- Return to your matrix visualization.
- You should now see the months sorted in the correct fiscal order, starting from August (1) and ending with July (12).
Common Issues and Solutions
Here are some common issues you might face and how to fix them:
- Incorrect sorting order even after applying the steps: Double-check that the Sort by Column option is applied to the correct column and that the Fiscal Month Order column is set to a whole number.
- Matrix not updating: If the changes don’t show immediately, try refreshing the dataset or the report.
- Wrong fiscal year mapping: Ensure the fiscal months are mapped correctly in the conditional column. For example, check that August is set to 1, and July is set to 12.
Conclusion
Sorting months based on a fiscal year instead of a calendar year in Power BI requires creating a custom fiscal month order column. By following the steps in this guide—creating a conditional column, sorting the month column by the fiscal order, and ensuring the correct data type—you’ll be able to display months in the proper order for your financial reports.
With these steps, your Power BI reports will align seamlessly with your organization’s fiscal year, ensuring accurate financial analysis and reporting.