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!

Advertisements

2 thoughts on “Sort months by first letter in PowerPivot and Power BI

  1. David 2017-05-15 / 13:45

    Great description, but is it possible to have customers in the columns when we have ca. 50 customers to show

    • Ivan Bondarenko 2017-05-15 / 18:43

      Hi David, I assume you can use the same approach for your customers table. Create calculated column “Relevant” with your own formula, then use it in filter area. Column “Relevant” will be recalculated on each refresh of your dataset.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s