How to assign fiscal month number

[Anonymous]

Hi,

Super basic question. My comany uses a fiscal year starting in July. How do I make a new column where the month of July is assigned number 1, August 2, etc. 

Thanks!

[Anonymous · Accepted solution]

@Anonymous  - You can add a new Custom Column in Power Query. 

Number.Mod(Date.Month([Date])+6,12)

Logic is: Find the month number, add 6, and finally take the remainder after dividing by 12.

Cheers!

Nathan

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

=IF(MONTH(Data[Date])<=6,YEAR(Data[Date])-1,YEAR(Data[Date]))

Hope this helps.

[corange]

Hi, 

I have tried this formula and it doesnt work for me It actually brings up the Year and not the month. 

do you have anything that will bring up the month? 

Thank you. 

[Ashish_Mathur]

Hi,

Try this calculated column formula

=FORMAT(Calendar[Date],"mmmm")

Hope this helps.

[corange]

Hi, 

I have used that formula. It gives me the Month - MMM in a date. For example 1/01/2012 will return JAN. 

What i am trying to achieve is to have Jan listed as 7 because this is it is Fiscal Year Number , July as 1, etc... 

[Ashish_Mathur]

Hi,

Create a 12 rowws by 2 column table with month name in the first column and order in the second column.  In the second column of this table, assign number from 1 to 6 for months from July to December and 7 to 12 for months from January to June.  Name this table as month_order.  In the Calendar Table, write this calculated column formula to extract month name from the Date column: Month Name = FORMAT(Calendar[Date],"mmmm").  Create a relationship from the month column of the Calendar Table to the month column of the month_order table.  In the Calendar Table, write this calculated column formula

=RELATED(Month_order[order])

Hope this helps.

[corange]

Thank you. This worked well.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Anonymous · Accepted solution]

@Anonymous  - You can add a new Custom Column in Power Query. 

Number.Mod(Date.Month([Date])+6,12)

Logic is: Find the month number, add 6, and finally take the remainder after dividing by 12.

Cheers!

Nathan

[Anonymous]

Hello,

This measure works fine until May But for the month of June it comes out as 0 rather 12th month 

Thank you

[EeeeenieMac]

MonthNo = 1+mod(month(datable[Date])-7,12)