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?
Solved! Go to Solution.
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.
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...
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.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!