Sort months by first letter in PowerPivot and Power BI

This post is a logical continuation of previous one where I described how to place measures in rows in Power BI table visual.

Here I again talk about trend tables that looks like

It displays several measures in a matrix with months, so we may see development of each measure.

Data viz gurus will say that such data must be on chart.

Right, but sometimes people want to see figures in tables and … nothing else.

One of the problems here is to display month first letters in right order.

Assume we have a calendar table.

If we add Month Letter using really just one letter, e.g. with following formula

We won’t be able to use sort rule by Month No

Due to appearing one-to-many relationship, Month Letter “J” starts months 1, 6, and 7. Sort cannot be done in this case.

But let’s use another formula – almost the same values, but with leading invisible characters, like space:

Or probably characters with other CharCodes can be used, that looks like ordinary English letters.

In this case values are different, and have 1-to-1 relation with month numbers.

Hence, sort can be done

However, even if you display only one year, column width makes sense.

1st table visual – with white textbox, 2nd – with [M Letter] (doesn’t look nice).

Nevertheless, such scenario is still valid in some cases. And now you know how to implement it.

Next problem you may face here – how to display only relevant months in trend table.

For example, you need to show Dec of previous year and YTD months of current year.

Or just current year, or last 12 months.

Each such scenario creates a strict logic that we can use in our Calendar table to build an indicator column.

For simplification of this sample, I work with calculated columns. However, same can be done in Query.

So, we need to add column that will help to filter out unnecessary months from resulting table.

Add to Calendar table new column with formula

In January I want to see full previous year and Dec of prior to it Year – to compare Year End results + see full KPI development during year.

Btw, same trick will work in PowerPivot too. Such column can be used as filter.

Calendar table with this column will look like:

And at this point KPI trend table like:

So it shows all months from Calendar table.

What left to do is to drag Relevant column to area of Visual level filters (when table visual is selected)

Then select ‘True’

Finally we got desired table

Sample file can be found here.

Any ideas for simplification are welcomed!