Change the order of months for a fiscal year

[Anonymous]

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 

[parry2k]

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

[mwegener]

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

[Anonymous]

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.  

[parry2k]

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

[Anonymous]

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! 

[parry2k]

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

[Anonymous]

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! 

[parry2k]

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

[Anonymous]

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 

[parry2k]

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

[Anonymous]

Thanks @parry2k !

When I try to sort I get this error

[parry2k]

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

[Anonymous]

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

[parry2k]

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

[Anonymous]

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

[parry2k · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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!