Power Pivot Principles: Sorting Financial Year Months in Power Pivot

Welcome back to the Power Pivot Principles blog. This week, we are going to learn a method of sorting the financial year months in Power Pivot.

When analysing data, sometimes you need to convert calendar month tables to financial year tables. There are multiple ways to achieve this with DAX functions. This week, we will use a simple way in Power Pivot to convert calendar months to financial year months for an example sales performance table.

Suppose we have the following Sales table (not displayed in full):

Next, we load the data in Power Pivot and generate the PivotTable based on fields Year, Calendar Month and Amount of Sales. The result would be:

The result is straightforward and the sales for each calendar month are clearly calculated. However, this result is not suitable for a company which has a year-end of 30 June, for example. In order to convert a calendar month table to a financial year month table, we create an additional column in Power Pivot.

This is a simple method to convert a calendar month to a financial year month.  Then, we export the result as PivotTable and choose the fields Year, FY Month and Amount of Sales and the result would be:

As indicated by the result, the first month of financial year starts with July and end with June. The result has been converted from calendar month to financial year month.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.