Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

How to assign fiscal month number

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!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@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

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

Write this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Write this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

Hi,

Try this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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... 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you. This worked well.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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
Not applicable

Hello,

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

Thank you

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.