Custom Date Formats in Power BI Using a Slicer

Ever thought of displaying dynamic date formats using a slicer in a Power BI dashboard?

Today in this blog I will be talking about how we can modify date formats dynamically in a Power BI dashboard. The blog includes usage of a disconnected table for these custom date formats, which can further be used as a slicer on the dashboard for a better user experience.

 

So, let us start with the following data model in the dashboard with just 2 tables – “Calendar” date table and “Clothing Sales” table. Both tables are related on a Date column as shown below:

I have got a simple table visual below showing daily sales: (“Date” column is taken from CALENDAR table and “Sales” come from CLOTHING SALES table)

Now the challenge here is, I need a way where I can dynamically change the format of the “Date” column displayed in the above table.

To achieve this, let us start by creating a disconnected table using “Enter Data” option in Power BI:

The resulting table is as follows:

I have created a “format” column with the basic date formats and an “Index” column which can be ignored.

Now we create a simple measure using the above disconnected table:

1. Lines 1-3 define the measure name; 2 variables are declared. 1 variable to take values from our disconnected table’s “format” column and the other one from “Date” column from the Calendar table.
2. Line 5-14 are parts of a SWITCH statement, where we are just checking for different cases on what date formats should be the output when a certain format is selected.

Now, simple task is to move this calculated measure to our table visual:

We see that currently; this measure is showing BLANK values. The reason is that this measure will output a value only when a selection is made on a “Format” column from our disconnected table.

We will therefore create a slicer using this “Format” column:

I have done some formatting to the slicer to show them as buttons, but you can create it the way you prefer it. Let us keep both the visuals side-by-side on the report:

Now, let us start making selections from this slicer and see how the table visual behaves:

So, when we start making selections from the slicer, our measure “Date Display” in the table visual starts to display dates in the selected format corresponding to the actual “Date” column in the visual. So, that’s a quick trick of achieving this behaviour in Power BI.

Now, let us try some hack here. Try removing the “Date” column from the table visual as follows:

Let us check how the slicer behaves:

We see that nothing is happening. The reason is – “Date Display” measure is driven by the slicer using the “Date” value from Calendar table. If we move the “Date” column back to the visual, it behaves perfectly fine.

Suppose I do not want users to see the actual “Date” column and only display the “Sales” against the new calculated measure “Date Display”. We can do this by a small hack. Just move the “Date” column back, but this time in the end of the table visual, so we have it as follows:

Let us just make some formatting changing to this table visual, so it is easy to hide the “Date” column in the end. Go to format options for this visual and do the following:

• Under column headers –> turn off ‘Auto-size column width’.
• Under column headers –> Turn off “Word wrap”.

Similarly, go to Values –> turn off “Word wrap” as shown below:

Now just adjust the width of “Date” column in this table visual, so that it just easy to hide and increase the width of “Date Display”:

Just rename the “Date Display” to “DATE” and we finally achieve the required output:

 

So, this is a small trick on how we can display dates in different date-formats in Power BI using a slicer.

Hope this quick trick helps everyone.

 

– Pragati