Sorting Date Table Columns In Power BI

In today’s blog, I’ll discuss a question that comes up all the time in the Enterprise DNA forum. How can you can sort the fields from an extended date table columns properly? You may watch the full video of this tutorial at the bottom of this blog.

This refers to the extended date table from the M code showcase that was developed by Melissa Dekorte, one of our Enterprise DNA experts.

Our example on this blog post is just a simple application that runs from 2018 to 2020 with a fiscal year that starts in July. The member wanted to learn how to sort the Month Name; they were also trying to sort one of the fields related to month.

This is how you can sort a date table column effectively.

Our extended date table has a lot of fields in it; frankly, they’re hard to keep in order sometimes.

This is why we created this cheat sheet that shows you what each of the fields looks like, as well as their format and content, on any given day.

In most cases, you can use this to figure out how to sort one field based on another.

We have the Month Name and the forum member was trying to use MonthnYear to sort it out.

Let’s find Month Name in our extended date table. Select Month Name in the Fields pane, and then go to Sort by column, and look for MonthnYear.

When we click on that, we get an error message that says we can’t have more than one value in MonthnYear for the same value in Month Name.

This comes down to a granularity problem. You need a one-to-one relationship between the field you’re trying to sort and the field you’re using to sort by.

In this case, for a given month in 2018, 2019, and 2020, we can have three different values for the MonthnYear field. This is why we need to find one that has a one-on-one relationship.

If we go back to our cheat sheet, we can see that MonthofYear is just the number of the month. This will have the one-to-one relationship that we’re looking for. This means January will correspond with 1, February with 2, and so on.

If we click on Sort by Column and sort by Month of Year, we get the field or table columns sorted out properly.

The next one, Month & Year, is a little more difficult. It is a compound field that has a short month and year.

We need to find something that sorts first by year, and then by month. If we click on MonthnYear in the Fields pane and go to Sort by Column, the MonthnYear that we originally tried to use is going to be a perfect fit.

You can see that for the Month & Year table column, everything has sorted out perfectly.

So now we have the first two done. However, the last one is a really ill-behaved field.

It’s problematic for a couple of reasons. We have two text fields concatenated together and the fiscal month field is not padded.

Ultimately, we want to achieve something similar to this MonthnYear, where we have a four-digit fiscal year and then our two-digit fiscal month padded after that.

The easiest way to do this is by using the power query.

We go to Transform data.

And then we go to our Dates table and add a Custom Column.

Let’s call this custom column Fiscal Year Fiscal Month Sort (FYFM Sort).

We’ll start with our text prefix which is 20 for the first two digits of our year and then we’ll concatenate that with a function called Tex.Middle. This function pulls a substring out of a larger text string. It’s analogous to the DAX function MID, and I’ll show you the difference between the two.

We’re going to use this on our fiscal year field.

In DAX, when we pull substrings, it’s a one-based index. To pull the third character, we will need to use 3. In power query, it’s a zero-based index so the third character will be 012.

We start with 2, which is the third character. We want to return two characters to get that second pair of digits off the fiscal year field.

The next step is to concatenate this with our padded fiscal month field.

In DAX, we will need to use a LENGTH function and IF function, then we can manually pad.

In power query, there’s a function that does all of that. This is the Text.PadStart.

Since the fiscal period is a numeric field, we’ll need to use a function called Text.From to convert the numeric value into a text value that we can pad. Then we’ll pull our fiscal period field.

We want to make sure that it has 2 characters. In cases where it has 1 character, we’re going to pad it with a 0.

Once we click the Okay button, we’ll have our four-digit fiscal year and then our two-digit padded fiscal month.

We’ll then go back to Home and click Close & Apply.

If we sort our FM & FY field by column, it will now sort perfectly by fiscal year and then a fiscal month.

Conclusion

In this blog, I’ve covered the technique on how to sort date table columns in Power BI. This is a good strategy to do for difficult fields, which came from building a few custom functions. I hope you have found this general set of tools helpful in your own report development.

If you haven’t done so already, please subscribe to the Enterprise DNA T V channel, where we have more content coming out in the future.

Cheers!

Sam

[youtube https://www.youtube.com/watch?v=DY5IWLcgBGA?rel=0&w=784&h=441]