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 automa...
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
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.
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.
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.??