- Ciao, friends.
In this video, I want to show you a useful technique
to sort month in fiscal calendars.
Let's start from the scenario.
I have a report that's already showing month, years,
and this is using just the standard regular time.
So, January to December, 2007, 8 and 9.
Now, in my date table, I have not only the standard year.
I also have a fiscal year.
And the fiscal year starts on the first of July.
So, if I look at my calendar table,
I have the year,
the month and I also have the fiscal year.
If I want to produce a report that shows the data,
not by calendar year, but instead does it by fiscal year,
I can simply remove the calendar year
and replace it with the fiscal year.
So, I get rid of the year and instead I put
on the columns the fiscal year.
The report works fine and it's showing numbers.
But the thing is that the numbers are wrongly sorted.
Why is that?
Because my fiscal calendar starts in July, I don't want
to see January, February, March, and April as the beginning.
They're months before July.
July should be the first month
and January should be the seventh month.
Otherwise, the data is not sorted.
Obviously, if you use numbers for fiscal calendar
the problem disappears.
But if you still want to see January to December,
then you need to sort the months in a different way.
Now what we need to do is first, create a new column
that we will use to sort the months.
But then we will face a problem,
because we cannot sort the month name
using two different options.
Sometimes the standard Gregorian calendar,
and sometimes the fiscal calendar.
So, we will need to duplicate the month.
So to have a fiscal month, which is identical
to the month, but uses a different sort order.
Let's do it together.
First of all, we need to create the sorter column.
So, in my date table, I have the month number.
I need to create the fiscal month number.
And to do that, I can simply create a new column.
Let's go to new column.
And let's call it fiscal month number.
How do I compute it?
Well, all the months which are less or equal than six,
they need to go six steps ahead.
And all the months that are more than seven,
they need to go six steps back.
So, it's just an IF statement that checks
if the month number is greater or equal six,
then it's the month number
minus six - actually, this should be greater than six.
Otherwise, it's the month number plus six.
And you see that now, the month number seven
suddenly became one.
And it will grow up to six, and then January being one,
it will become seven.
So now I have my fiscal month number.
I don't want to sort the month by fiscal month number,
because the month is already correctly set
as sorted by the month number.
So, I need to create a new column which is easy to do.
I simply go here and create a new column,
which I call the fiscal month.
That's nothing but a shallow copy of the month.
I don't want to change the content,
I only want to change the sort order.
So now on the fiscal month, I can go here.
In column tools, I will find the sort by column,
and I sort it by fiscal month number.
And in my report, I simply get rid of the month
and I replace it with the fiscal month.
I don't see whether it's a number or a string.
The first one is the fiscal month,
so I simply take it, drop it here
and now you see the report works nicely.
It starts from January, goes up to June,
and then July 2007 goes on the next month
as the first month of the year.
So, this is a simple and useful technique
whenever you need to have a column that might be sorted
in multiple ways, it's kind of easy.
You just duplicate the column, create the sorter column,
and use the sort by column on the new column.
Enjoy DAX!