Power BI Blog: Dating Part 5

Welcome back to this week’s edition of the Power BI blog series. This week, we create a month name column and add it to the hierarchy for Calendar1.

In Power BI Blog: Dating Part 2, we generated a simple Date Table using the DAX CALENDAR() function, which we called Calendar1. We used Calendar1 to link the other tables in our simple Data Model.

This enabled us to display the data from Costs and Sales correctly in a Matrix visualisation:

We completed Calendar 1 by marking it as a Date Table and adding more columns in Power BI Blog: Dating Part 3.

We created the Year Hierarchy in Power BI Blog: Dating Part 4:

This allowed us to drill down and up in the Matrix visualisation:

We need to refine the Calendar1 columns to make them easier to interpret. Month numbers aren’t very user friendly in a Matrix: it would be easier to interpret the data if we use the month name instead. We can do this by adding a new column to Calendar1 in the Data view:

The new column will be called ‘Month Name’:

The DAX code we have used is:

This extracts the month from the date in the format ‘MMM’.  This format is ideal for the Matrix, as each column will be roughly the same width.

In the Model View, we need to amend the Year Hierarchy so that it includes Month Name instead of Month Number.  We start by dragging Month Name into Year Hierarchy in the Fields pane:

We select Year Hierarchy, and in the Properties pane, we can delete Month Number using the cross next to the field. Note that no changes will be made to Year Hierarchy until we ‘Apply Level Changes’.

There is a feature when making changes here, which we could call a bug! We can click and drag the fields to reorder them within Year Hierarchy, but at this point these changes will not be saved, as the level deletion is performed first.

We can now choose to ‘Apply Level Changes’. Power BI prompts us to confirm the deletion:

We click OK, and the deletion occurs, but the change to the order of the hierarchy levels has not been saved:

We click and drag again:

This time when we ‘Apply Level Changes’, the order is changed:

When we go back to the Report View, we may expect to see the Month Name in the Matrix:

Clearly, we have more work to do! Next time, we will work on the Matrix to resolve the issues…

Check back next week for more Power BI tips and tricks!