Dax formula to change the first month of fiscal year.

[shophoney]

Hi I have a dCalendar table with all my date. But our financial year starts feb 1. So January is the 12 month of our year and February is the 1st month.

I want to create a sort order in the table.

January 2019 should be listed as month 12 of the 2018 year.

Thanks

 

[Matt Allington]

You can create your own calendar table with what ever you need in it. I show you how here Create a Custom Calendar in Power Query - PowerPivotPro

 

[shophoney]

You can create your own calendar table with what ever you need in it. I show you how here Create a Custom Calendar in Power Query - PowerPivotPro

Hi Matt. Great job on the instructions.

I've already created a table. My issue is our first month of the financial year is february and the last is january.

So february is 1 and january is 12. But i want to index them properly and also have january 2017 as month 12 of the 2016 financial year.

I hope this is clear,

thanks

 

[Matt Allington]

It was already clear . Just write a conditional column to change the default to what ever you want. Eg

if column = 1 then 12 else column - 1

 

[shophoney]

Oh. I went to put in your suggestions. And saw i already had this:

=IF(MONTH([DATES])-1=0,12,(MONTH([DATES])-1))

But in order to have January be with the following year. What would you suggest another column adding in the YEAR? How do you push January back?

Thanks

 

[Matt Allington]

I did reply to this earlier, but something must have happened to it. Yes, you need a calendar year column and then convert that to a fin year column using a similar approach to the one above.