cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

1 ACCEPTED SOLUTION
Super User

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

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

MVP

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

Please mark my post as solution, this will also help others.

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

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.

Super User

@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!
Super User

@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!

Super User

@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

Super User

@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

Super User

@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" )
but am still getting the same error.  Also, Dec 2017 records are all coming out as 2017-11 after applying the formula.

Super User

@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?

Super User

@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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors