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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Change the order of months for a fiscal year

Hi,

 

I'm working visuals for sales ffrom FY2017 through FY2019.  Our fiscal year is Dec 1st - Nov. 30th. I already had a month field, and created a numeral month field and used 'Month = FORMAT(DATE(1, [Numeral Month], 1), "MMM")' to change the order.  That worked, but how to I change the numeric month order to start with Dec as month 1 through Nov as month 12? I know it's probably very simple, but I can't figure it out. Thanks!  -Stephanie Screenshot.jpg

1 ACCEPTED SOLUTION

@Anonymous you can share pbix file thru one drive link or send directly to my email (it is in my signature)



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

18 REPLIES 18
parry2k
Super User
Super User

@Anonymous add calculated column like this

 

FY Month Number = 
IF ( Table[MonthNumber] = 12, 1, Table[MonthNumber] + 1 )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mwegener
Most Valuable Professional
Most Valuable Professional

Hi @Anonymous ,

 

check this.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column#sort-using-the-sort-by-column-button

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Anonymous
Not applicable

Thanks for reacing out to help.  I actully used the video to initally learn to sort defferently so tht months wouldn't be in alplabetical roder 🙂  Since our fisal year is a month ahead of most, I have to change numeric order now.  

@Anonymous so did you used the Fiscal Month Number logic I provided?

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi!  Thanks for your help! I actually found this option and got it work.  

Month_Number =
SWITCH (
[MONTH],
"Aug", 09,
"Dec", 01,
"Jul", 08,
"Nov", 12,
"Oct", 11,
"Sep", 10,
"Apr", 05,
"Feb", 03,
"Jan", 02,
"Mar", 04,
"May", 06,
"Jun", 07
)
You definitely got me on the right track.  Thanks again! 

@Anonymous That will work but I will not do that rather the column expression I gave you that will work, you just need to calculate month number from your data and a single line code will work rather this switch statement, although it is not a big thing, it is not something will change ever but it seems too much for such a simple task.


Anyhow, you have the solution, it matters the most.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

If you can provide instructions on how to do it, I will try.  When I tried it orginally from you post I was getting an error, so I tried the switch method. 

 

Thanks! 

@Anonymous I think in your table you have a column called  Numeral Month use that in the following column

 

FY Month Number = 
IF ( Table[Numeral Month] = 12, 1, Table[Numeral Month] + 1 )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Good mmorning @parry2k!

 

The formular you provided worked great, thanks again.  I have question, I have fiscal year (2017, 2018 & 2019) in my data and the months (numeral month, text and FY Month Number).  How can I have the FY Month Number go in oder of fiscal year and month? 

ie.

Nov 2017 =1

Dec 2018 =2 

Jan 2018 =3

Feb 2018 = 4

through Nov 2019 = 24

 

Thank you! - Stephanie 

 

@Anonymous create a new sort column with following expression and use this to sort fiscal month

 

Fiscal Month Sort = 
FORMAT( Table[Fiscal Year], "####" ) & "-" & FOMAT( Table[Fiscal Month Number], "##" )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks @parry2k !

 

When I try to sort I get this error

Image.JPG

@Anonymous Fiscal Month Sort value doesn't look correct.

 

Fiscal Month Sort = 
FORMAT( Table[Fiscal Year], "####" ) & "-" & FOMAT( Table[Fiscal Month Number], "0#" )

 

The value should be 2017-01, 2017-02, 2017-03 and so on.....

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k   I updated it to:

Fiscal Month Sort = FORMAT( Table1[Fiscal Year], "####" ) & "-" & FORMAT( Table1[FY Month Number], "01" )
Image3.JPG
but am still getting the same error.  Also, Dec 2017 records are all coming out as 2017-11 after applying the formula.  
 
image2.JPG

@Anonymous share pbix file, remove sensitive information before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k I can pnly publish the pbix file to my own dashboard, not to the web for viewing.  Are there any other options I can try? 

@Anonymous you can share pbix file thru one drive link or send directly to my email (it is in my signature)



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k Great!  I just emailed you.  Thank you!! 

Anonymous
Not applicable

Thank you SO VERY much!!!  You are a huge help!  I really appreciate you helping and have the patience for a newbie like me 🙂  I have experience with other platforms that use scripts, but Power bi is a bit different.  Again THANK YOU! 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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