Forum Discussion
Solved
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
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
8 Replies
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
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.
Hi Phil_Seamark. Thank You for having a look at this problem. Please let me know if you found something.
Thank You
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
