cancel
Showing results for 
Search instead for 
Did you mean: 
Pragati11

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:

df1.png

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)

df2.png

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:

df3.png

The resulting table is as follows:

df4.png

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:

df5.png

  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:

df6.png

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:

df7.png

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:

df8.png

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

df9.gif

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:

df10.png

Let us check how the slicer behaves:

df11.gif

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:

df12.png

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”.

df13.png

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

df14.png

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”:

df15.gif

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

df16.gif

 

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

Comments

Olá, desculpe pelo comentario que farei fora do contexto desse Forum. Mas preciso de ajuda, 

 

Estou fazendo um curso na Udemeny de Power BI, quero me profissionalizar e trabalhar na area,  ja utilizo analise de dados na area comercial, podem me indicar um curso on-lline com certificado e que seja profissionalizante?  Quero atuar na area. 

 

Agradeço e muito obrigado. 

Hi @Sotto ,

 

Just check out following link where there are references to few of the top Power BI courses:

https://solutionsreview.com/business-intelligence/the-best-power-bi-training-and-online-courses/

 

 

Thanks,

Pragati

Polls
What is your favorite Power BI Feature release this month?