Showing Dates in reverse order for Tables/Matrix with dates, so that the latest is always shown first in a Power BI Matrix
While doing some work I had a matrix that I had created. What I found was that when I had more Yr-Month values that could fit in the matrix due to having the scroll bar it would show me the older Yr-Months and not the latest ones as shown below.
I always like to think what the experience would be like for the report consumer. And if I was using this report to have to scroll each and every time to see the latest data would be a pain to say the least.
What I came up with was a relatively easy solution but made the reporting experience that much better. Where it will display the Yr-Months in descending order (The current month first)
Below are the steps I completed to achieve this.
Creating the Yr-Month Desc column and Sort by column
The first thing I had to do was to create the new column which I could then use to sort the Yr-Month desc
I duplicated my current Yr-Month column and renamed it to ‘Yr-Month Desc’
Next, I went into my Power Query Editor and created the following new column in my date table.
- I created the new column as shown below.
- What I did was to subtract 100 from the concatenation of the Year and Month Number, so that the numbers would be in reverse order.
- 100 – Number.From(Text.From([Calendar Year]) & Text.PadStart(Text.From([Calendar Month Number]),2,”0″))
This is what it looked like once done, I also changed the data type to Whole Number.
The final step was to configure the Sort By to my column ‘Yr-Month Desc’
Viewing the Matrix with the Yr-Month Desc
Below is the final output and what it looked like once it has been configured.
You can see both the initial version and the updated version
As you can see above this makes it a lot easier and quicker for the report consumers to view the data, they need to see without having to scroll.
It is often these small things that have the biggest impact for adoption of Power BI reports.
I hope you enjoyed this blog post, thanks for reading and if there are any questions please let me know.