cancel
Showing results for
Did you mean:
Helper II

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

2 ACCEPTED SOLUTIONS
Resolver IV

Formula should be:

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

Helper II

Thanks, that does it 🙂

10 REPLIES 10
Resolver IV

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.

Helper II

Thanks,

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

Resolver IV

In a Date table

Helper II

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

Resolver IV

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

Helper II

Nope

Resolver IV

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.

Helper II

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.

Resolver IV

Formula should be:

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

Helper II

Thanks, that does it 🙂

Announcements