cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wenners68
Helper II
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

Formula should be:

 

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

View solution in original post

Thanks, that does it 🙂

View solution in original post

10 REPLIES 10
RolandsP
Resolver IV
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.

Thanks,

 

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

In a Date table

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:

 

Capture.PNG

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

 

Capture2.PNG

 

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

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

Capture.PNG

 

Nope

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.

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.

Formula should be:

 

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

View solution in original post

Thanks, that does it 🙂

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors