Forum Discussion

wenners68's avatar
wenners68
Helper II
8 years ago
Solved

Order column chart by month over multiple years

I'm trying to order the columns by month over a financial year that starts in May and finishes in April. Most examples I have come across only deals with January to December scenarios. Any ideas ho...
Need Help
Show and Tell
Tips and Tricks
  • RolandsP's avatar
    RolandsP
    8 years ago

    Formula should be:

     

    Financial month number = IF(DateKey[Month number]-4<=0,DateKey[Month number]+8,DateKey[Month number]-4)

  • wenners68's avatar
    wenners68
    8 years ago

    Thanks, that does it :-)

RolandsP's avatar
RolandsP
Resolver IV
8 years ago

First, you need Date table.

Second, you need to create a column FYMonthNum and assign value 1 to May, value 2 to June, and so on until value 12 for April.

Third, you need to apply that Month column is sorted by this newly created column FYMonthNum.

 

If you have done all this, then in the visual month should appear correctly from May until April.

  • wenners68's avatar
    wenners68
    Helper II
    8 years ago

    Thanks,

     

    Do I add the FYMonthNum column to the Date table or to the table with the sales data?

    • RolandsP's avatar
      RolandsP
      Resolver IV
      8 years ago

      In a Date table

      • wenners68's avatar
        wenners68
        Helper II
        8 years ago

        Hi, that doesn't work.

         

        This is what I've done so far: I've created a date table with a variety of columns, including MonthY (Year+Month number), related the sales table and the date table. I then try to sort Month by using the MonthY field. And this is what I get:

         

        This is how the date table looks like (a cut down version):

         

         

        I've also tried sorting by the Index field, with the same error result. I expected this to be something that was easy to do, but it's proving surprisingly hard...