Order column chart by month over multiple years

[wenners68]

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 how I can do this?

[RolandsP · Accepted solution]

Formula should be:

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

[RolandsP]

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]

Thanks,

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

[RolandsP]

In a Date table

[wenners68]

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

[RolandsP]

You have field Financial Month Number. Sort Month column by that field.

[wenners68]

Nope

[RolandsP]

Then you need to check value in this field. Based on the chart you sent for Jan you have number 5, although it should be 9.

[wenners68]

Yes, you're correct. I used the following calculation to get the financial month:

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

Problem being that my financial starts in May (month #5) and finishes in April (month #4), so the calc above won't cope with that scenario.

[RolandsP · Accepted solution]

Formula should be:

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

[wenners68 · Accepted solution]

Thanks, that does it 🙂