cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bjoshi Resolver I
Resolver I

Sorting Month Automatically Based on Financial Year End

I have a month table and multiple columns representing month number based on financial year end date. 

I can manually sort the month column based on those other columns. But I want it to sort automatically based on a column value from another table.

For example:

Month	YearEnd_Jan	YearEnd_Feb	YearEnd_Mar	
Jul	             06	             05	              04	
Aug	             07	             06	              05	
Sep	             08	             07	              06	
Oct	             09	             08	              07	
Nov	             10	             09	              08	
Dec	             11	             10	              09	
Jan	             12	             11	              10	
Feb	             01	             12	              11	
Mar	             02	             01	              12	
Apr	             03	             02	              01	
May	             04	             03	              02	
Jun                05	             04	              03	
organisation  finanacial_year_end
ABC                            2    

 

 I want to sort the Month column automatically from the first table by the column YearEnd_Feb since the second table has financial_year_end value 2.

 

Thank You

 

1 ACCEPTED SOLUTION

Accepted Solutions
bjoshi Resolver I
Resolver I

Re: Sorting Month Automatically Based on Financial Year End

I solved this by making the MonthNumber dynamic so that the Month can always be sorted by the same column.

 

First, I added a custom column with organisation name that comes from a parameter, then merged the table with Organisation table to expand the FinancialYearEndofMonth. Then, I added the MonthNumber custom column using the M query below and sorted the Month column by MonthNumber.

 

M query to make the Month Number dynamic: 

if[FinancialYearEndMonth]=1 then
	  if [Month] = "Feb"  then 1
	 else if [Month] = "Mar"  then 2
	  else if [Month] = "Apr"  then 3
	  else if [Month] = "May"  then 4
	  else if [Month] = "Jun"  then 5
	  else if [Month] = "Jul"  then 6
	  else if [Month] = "Aug"  then 7
	  else if [Month] = "Sep"  then 8
	  else if [Month] = "Oct"  then 9
	  else if [Month] = "Nov"  then 10
	  else if [Month] = "Dec"  then 11
	  else if [Month] = "Jan"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=2 then
	  if [Month] = "Mar"  then 1
	  else if [Month] = "Apr"  then 2
	  else if [Month] = "May"  then 3
	  else if [Month] = "Jun"  then 4
	  else if [Month] = "Jul"  then 5
	  else if [Month] = "Aug"  then 6
	  else if [Month] = "Sep"  then 7
	  else if [Month] = "Oct"  then 8
	  else if [Month] = "Nov"  then 9
	  else if [Month] = "Dec"  then 10
	  else if [Month] = "Jan"  then 11
	  else if [Month] = "Feb"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=3 then
	 if [Month] = "Apr"  then 1
	  else if [Month] = "May"  then 2
	  else if [Month] = "Jun"  then 3
	  else if [Month] = "Jul"  then 4
	  else if [Month] = "Aug"  then 5
	  else if [Month] = "Sep"  then 6
	  else if [Month] = "Oct"  then 7
	  else if [Month] = "Nov"  then 8
	  else if [Month] = "Dec"  then 9
	  else if [Month] = "Jan"  then 10
	  else if [Month] = "Feb"  then 11
	  else if [Month] = "Mar"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=4 then
	   if [Month] = "May"  then 1
	  else if [Month] = "Jun"  then 2
	  else if [Month] = "Jul"  then 3
	  else if [Month] = "Aug"  then 4
	  else if [Month] = "Sep"  then 5
	  else if [Month] = "Oct"  then 6
	  else if [Month] = "Nov"  then 7
	  else if [Month] = "Dec"  then 8
	  else if [Month] = "Jan"  then 9
	  else if [Month] = "Feb"  then 10
	  else if [Month] = "Mar"  then 11
	  else if [Month] = "Apr"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=5 then
	  if [Month] = "Jun"  then 1
	  else if [Month] = "Jul"  then 2
	  else if [Month] = "Aug"  then 3
	  else if [Month] = "Sep"  then 4
	  else if [Month] = "Oct"  then 5
	  else if [Month] = "Nov"  then 6
	  else if [Month] = "Dec"  then 7
	  else if [Month] = "Jan"  then 8
	  else if [Month] = "Feb"  then 9
	  else if [Month] = "Mar"  then 10
	  else if [Month] = "Apr"  then 11
	  else if [Month] = "May"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=6 then
	  if [Month] = "Jul"  then 1
	  else if [Month] = "Aug"  then 2
	  else if [Month] = "Sep"  then 3
	  else if [Month] = "Oct"  then 4
	  else if [Month] = "Nov"  then 5
	  else if [Month] = "Dec"  then 6
	  else if [Month] = "Jan"  then 7
	  else if [Month] = "Feb"  then 8
	  else if [Month] = "Mar"  then 9
	  else if [Month] = "Apr"  then 10
	  else if [Month] = "May"  then 11
	  else if [Month] = "Jun"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=7 then
	  if [Month] = "Aug"  then 1
	  else if [Month] = "Sep"  then 2
	  else if [Month] = "Oct"  then 3
	  else if [Month] = "Nov"  then 4
	  else if [Month] = "Dec"  then 5
	  else if [Month] = "Jan"  then 6
	  else if [Month] = "Feb"  then 7
	  else if [Month] = "Mar"  then 8
	  else if [Month] = "Apr"  then 9
	  else if [Month] = "May"  then 10
	  else if [Month] = "Jun"  then 11
	  else if [Month] = "Jul"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=8 then
	  if [Month] = "Sep"  then 1
	  else if [Month] = "Oct"  then 2
	  else if [Month] = "Nov"  then 3
	  else if [Month] = "Dec"  then 4
	  else if [Month] = "Jan"  then 5
	  else if [Month] = "Feb"  then 6
	  else if [Month] = "Mar"  then 7
	  else if [Month] = "Apr"  then 8
	  else if [Month] = "May"  then 9
	  else if [Month] = "Jun"  then 10
	  else if [Month] = "Jul"  then 11
	  else if [Month] = "Aug"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=9 then
	 if [Month] = "Oct"  then 1
	  else if [Month] = "Nov"  then 2
	  else if [Month] = "Dec"  then 3
	  else if [Month] = "Jan"  then 4
	  else if [Month] = "Feb"  then 5
	  else if [Month] = "Mar"  then 6
	  else if [Month] = "Apr"  then 7
	  else if [Month] = "May"  then 8
	  else if [Month] = "Jun"  then 9
	  else if [Month] = "Jul"  then 10
	  else if [Month] = "Aug"  then 11
	  else if [Month] = "Sep"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=10 then
	 if [Month] = "Nov"  then 1
	  else if [Month] = "Dec"  then 2
	  else if [Month] = "Jan"  then 3
	  else if [Month] = "Feb"  then 4
	  else if [Month] = "Mar"  then 5
	  else if [Month] = "Apr"  then 6
	  else if [Month] = "May"  then 7
	  else if [Month] = "Jun"  then 8
	  else if [Month] = "Jul"  then 9
	  else if [Month] = "Aug"  then 10
	  else if [Month] = "Sep"  then 11
	  else if [Month] = "Oct"  then 12
	 else if [Month] = null then 0
	else 0
 else if[FinancialYearEndMonth]=11 then
	  if [Month] = "Dec"  then 1
	  else if [Month] = "Jan"  then 2
	  else if [Month] = "Feb"  then 3
	  else if [Month] = "Mar"  then 4
	  else if [Month] = "Apr"  then 5
	  else if [Month] = "May"  then 6
	  else if [Month] = "Jun"  then 7
	  else if [Month] = "Jul"  then 8
	  else if [Month] = "Aug"  then 9
	  else if [Month] = "Sep"  then 10
	  else if [Month] = "Oct"  then 11
	  else if [Month] = "Nov"  then 12
	  else if [Month] = null then 0
	  else 0
else if[FinancialYearEndMonth]=12 then
	  if [Month] = "Jan"  then 1
	  else if [Month] = "Feb"  then 2
	  else if [Month] = "Mar"  then 3
	  else if [Month] = "Apr"  then 4
	  else if [Month] = "May"  then 5
	  else if [Month] = "Jun"  then 6
	  else if [Month] = "Jul"  then 7
	  else if [Month] = "Aug"  then 8
	  else if [Month] = "Sep"  then 9
	  else if [Month] = "Oct"  then 10
	  else if [Month] = "Nov"  then 11
	  else if [Month] = "Dec"  then 12
	  else if [Month] = null then 0
	else 0
else 0

View solution in original post

8 REPLIES 8
Microsoft Phil_Seamark
Microsoft

Re: Sorting Month Automatically Based on Financial Year End

Hi @bjoshi, this is an intruiging question and I did have a bit of a play around with a column like your Month column being sorted by a calculated column.

 

I could partially get it to work but seemed to fall down when I tried to add the final condition of using a value from a filtered table.  With more time I may get this going but it will fall firmly in the hack/workaround camp.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

bjoshi Resolver I
Resolver I

Re: Sorting Month Automatically Based on Financial Year End

Hi @Phil_Seamark. Thank You for having a look at this problem. Please let me know if you found something.

Thank You

Microsoft Phil_Seamark
Microsoft

Re: Sorting Month Automatically Based on Financial Year End

One idea could be to have 3 columns with 36 rows like so.

 

And join your other table to this using the MonthID, so when filtered, you only ever have 12 rows showing.

 

You can then probably sort [Month] by [Sort Order] to potentially get what you need.....  What do you think?

 

Month	MonthID Sort Order
JAN	1	1
FEB	1	2
MAR	1	3
APR	1	4
MAY	1	5
JUN	1	6
JUL	1	7
AUG	1	8
SEP	1	9
OCT	1	10
NOV	1	11
DEC	1	12
JAN	2	7
FEB	2	8
MAR	2	9
APR	2	10
MAY	2	11
JUN	2	12
JUL	2	1
AUG	2	2
SEP	2	3
OCT	2	4
NOV	2	5
DEC	2	6
JAN	3	10
FEB	3	11
MAR	3	12
APR	3	1
MAY	3	2
JUN	3	3
JUL	3	4
AUG	3	5
SEP	3	6
OCT	3	7
NOV	3	8
DEC	3	9

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft Phil_Seamark
Microsoft

Re: Sorting Month Automatically Based on Financial Year End

Hmm, I don't think that will work for you sorry (the 36 row solution).  I just tried a quick test.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
bjoshi Resolver I
Resolver I

Re: Sorting Month Automatically Based on Financial Year End

Yea. And that the 3 months are just an example. The financial year end could be any month. So, will have to cater for 12 months.

Microsoft Phil_Seamark
Microsoft

Re: Sorting Month Automatically Based on Financial Year End

Could try creating 3 different [Month] tables.  Each one sorted as appropriate and then using the USERELATIONSHIP function to pick the appropriate one based on the filter. 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

bjoshi Resolver I
Resolver I

Re: Sorting Month Automatically Based on Financial Year End

That sounds like a good idea. I am not really sure how USERELATIONSHIP works.

In this case, how would you choose which Month table among all the table to use in the axis.??

bjoshi Resolver I
Resolver I

Re: Sorting Month Automatically Based on Financial Year End

I solved this by making the MonthNumber dynamic so that the Month can always be sorted by the same column.

 

First, I added a custom column with organisation name that comes from a parameter, then merged the table with Organisation table to expand the FinancialYearEndofMonth. Then, I added the MonthNumber custom column using the M query below and sorted the Month column by MonthNumber.

 

M query to make the Month Number dynamic: 

if[FinancialYearEndMonth]=1 then
	  if [Month] = "Feb"  then 1
	 else if [Month] = "Mar"  then 2
	  else if [Month] = "Apr"  then 3
	  else if [Month] = "May"  then 4
	  else if [Month] = "Jun"  then 5
	  else if [Month] = "Jul"  then 6
	  else if [Month] = "Aug"  then 7
	  else if [Month] = "Sep"  then 8
	  else if [Month] = "Oct"  then 9
	  else if [Month] = "Nov"  then 10
	  else if [Month] = "Dec"  then 11
	  else if [Month] = "Jan"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=2 then
	  if [Month] = "Mar"  then 1
	  else if [Month] = "Apr"  then 2
	  else if [Month] = "May"  then 3
	  else if [Month] = "Jun"  then 4
	  else if [Month] = "Jul"  then 5
	  else if [Month] = "Aug"  then 6
	  else if [Month] = "Sep"  then 7
	  else if [Month] = "Oct"  then 8
	  else if [Month] = "Nov"  then 9
	  else if [Month] = "Dec"  then 10
	  else if [Month] = "Jan"  then 11
	  else if [Month] = "Feb"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=3 then
	 if [Month] = "Apr"  then 1
	  else if [Month] = "May"  then 2
	  else if [Month] = "Jun"  then 3
	  else if [Month] = "Jul"  then 4
	  else if [Month] = "Aug"  then 5
	  else if [Month] = "Sep"  then 6
	  else if [Month] = "Oct"  then 7
	  else if [Month] = "Nov"  then 8
	  else if [Month] = "Dec"  then 9
	  else if [Month] = "Jan"  then 10
	  else if [Month] = "Feb"  then 11
	  else if [Month] = "Mar"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=4 then
	   if [Month] = "May"  then 1
	  else if [Month] = "Jun"  then 2
	  else if [Month] = "Jul"  then 3
	  else if [Month] = "Aug"  then 4
	  else if [Month] = "Sep"  then 5
	  else if [Month] = "Oct"  then 6
	  else if [Month] = "Nov"  then 7
	  else if [Month] = "Dec"  then 8
	  else if [Month] = "Jan"  then 9
	  else if [Month] = "Feb"  then 10
	  else if [Month] = "Mar"  then 11
	  else if [Month] = "Apr"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=5 then
	  if [Month] = "Jun"  then 1
	  else if [Month] = "Jul"  then 2
	  else if [Month] = "Aug"  then 3
	  else if [Month] = "Sep"  then 4
	  else if [Month] = "Oct"  then 5
	  else if [Month] = "Nov"  then 6
	  else if [Month] = "Dec"  then 7
	  else if [Month] = "Jan"  then 8
	  else if [Month] = "Feb"  then 9
	  else if [Month] = "Mar"  then 10
	  else if [Month] = "Apr"  then 11
	  else if [Month] = "May"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=6 then
	  if [Month] = "Jul"  then 1
	  else if [Month] = "Aug"  then 2
	  else if [Month] = "Sep"  then 3
	  else if [Month] = "Oct"  then 4
	  else if [Month] = "Nov"  then 5
	  else if [Month] = "Dec"  then 6
	  else if [Month] = "Jan"  then 7
	  else if [Month] = "Feb"  then 8
	  else if [Month] = "Mar"  then 9
	  else if [Month] = "Apr"  then 10
	  else if [Month] = "May"  then 11
	  else if [Month] = "Jun"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=7 then
	  if [Month] = "Aug"  then 1
	  else if [Month] = "Sep"  then 2
	  else if [Month] = "Oct"  then 3
	  else if [Month] = "Nov"  then 4
	  else if [Month] = "Dec"  then 5
	  else if [Month] = "Jan"  then 6
	  else if [Month] = "Feb"  then 7
	  else if [Month] = "Mar"  then 8
	  else if [Month] = "Apr"  then 9
	  else if [Month] = "May"  then 10
	  else if [Month] = "Jun"  then 11
	  else if [Month] = "Jul"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=8 then
	  if [Month] = "Sep"  then 1
	  else if [Month] = "Oct"  then 2
	  else if [Month] = "Nov"  then 3
	  else if [Month] = "Dec"  then 4
	  else if [Month] = "Jan"  then 5
	  else if [Month] = "Feb"  then 6
	  else if [Month] = "Mar"  then 7
	  else if [Month] = "Apr"  then 8
	  else if [Month] = "May"  then 9
	  else if [Month] = "Jun"  then 10
	  else if [Month] = "Jul"  then 11
	  else if [Month] = "Aug"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=9 then
	 if [Month] = "Oct"  then 1
	  else if [Month] = "Nov"  then 2
	  else if [Month] = "Dec"  then 3
	  else if [Month] = "Jan"  then 4
	  else if [Month] = "Feb"  then 5
	  else if [Month] = "Mar"  then 6
	  else if [Month] = "Apr"  then 7
	  else if [Month] = "May"  then 8
	  else if [Month] = "Jun"  then 9
	  else if [Month] = "Jul"  then 10
	  else if [Month] = "Aug"  then 11
	  else if [Month] = "Sep"  then 12
	  else if [Month] = null then 0
	else 0
else if[FinancialYearEndMonth]=10 then
	 if [Month] = "Nov"  then 1
	  else if [Month] = "Dec"  then 2
	  else if [Month] = "Jan"  then 3
	  else if [Month] = "Feb"  then 4
	  else if [Month] = "Mar"  then 5
	  else if [Month] = "Apr"  then 6
	  else if [Month] = "May"  then 7
	  else if [Month] = "Jun"  then 8
	  else if [Month] = "Jul"  then 9
	  else if [Month] = "Aug"  then 10
	  else if [Month] = "Sep"  then 11
	  else if [Month] = "Oct"  then 12
	 else if [Month] = null then 0
	else 0
 else if[FinancialYearEndMonth]=11 then
	  if [Month] = "Dec"  then 1
	  else if [Month] = "Jan"  then 2
	  else if [Month] = "Feb"  then 3
	  else if [Month] = "Mar"  then 4
	  else if [Month] = "Apr"  then 5
	  else if [Month] = "May"  then 6
	  else if [Month] = "Jun"  then 7
	  else if [Month] = "Jul"  then 8
	  else if [Month] = "Aug"  then 9
	  else if [Month] = "Sep"  then 10
	  else if [Month] = "Oct"  then 11
	  else if [Month] = "Nov"  then 12
	  else if [Month] = null then 0
	  else 0
else if[FinancialYearEndMonth]=12 then
	  if [Month] = "Jan"  then 1
	  else if [Month] = "Feb"  then 2
	  else if [Month] = "Mar"  then 3
	  else if [Month] = "Apr"  then 4
	  else if [Month] = "May"  then 5
	  else if [Month] = "Jun"  then 6
	  else if [Month] = "Jul"  then 7
	  else if [Month] = "Aug"  then 8
	  else if [Month] = "Sep"  then 9
	  else if [Month] = "Oct"  then 10
	  else if [Month] = "Nov"  then 11
	  else if [Month] = "Dec"  then 12
	  else if [Month] = null then 0
	else 0
else 0

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors