Pro Tip-Customising Data Tables in Power BI

Power BI provides many different ways to customise your dashboards and reports. Acclario’s BI Consultant @Andy Lee details steps to make your Power BI visualisations more compelling, interesting, and customised to your needs.

When working with a Power BI report have you ever wondered why you cannot get your visuals to:

• Filter by a date range
• Use Time Intelligence functions
• Categorise or name your date periods, or
• Analyse date and time-based transaction types from different tables together?

The reason could be because you are not using a dedicated date table.

A date table is a table which contains a set of unique and continuous range of dates for the period you wish to model. This table can also contain different date categorisations for your modelling.

Once you have created your date table, you can connect its date column with a relationship to the date columns of the other tables you wish to analyse. This will help resolve the above problems which you could be facing.

Follow the below steps to start building your model’s date table.

1. Create a new table

2. For the DAX formula, use the CALENDAR function, but pair it with functions which suits your model’s date range requirements:

• Static range
  • Date Table = CALENDAR ( DATE ( 2000, 01, 01 ), DATE ( 2030, 12, 31 ) )
• Dynamic range
  • Date Table = CALENDAR ( DATE ( 2000, 01, 01 ), TODAY ( ) )
  • Date Table = CALENDAR ( TODAY ( ) -800, TODAY ( ) +800 )
  • Date Table = CALENDAR ( MIN ( ‘Table Name’[Date_Column_Name] ), MAX ( ‘Table Name’[Date_Column_Name] ) )

 3. You can then create additional calculated columns for each different category you wish to add to be able to filter by, for example:

• Year = YEAR ( DATE ( ‘Date Table’[Date] )
• Month = MONTH ( DATE ( ‘Date Table’[Date] )
• Day = DAY ( DATE ( ‘Date Table’[Date] )
• Month Name = [Date].[Month]
• MonthYear Name = [Date].[Month] & ” ” & [Date].[Year]
• MonthShort Name = SWITCH( [Month], 1, “JAN”, 2, “FEB”, 3, “MAR”, 4, “APR”, 5, “MAY”, 6, “JUN”, 7, “JUL”, 8, “AUG”, 9, “SEP”, 10, “OCT”, 11, “NOV”, 12, “DEC”, “Invalid month” )

4. Lastly, create a one-to-many relationship between the new Date Table and the transaction tables you are modelling. You are now ready to use the Date Table for dates in your visualisations!

Find out more about our Data and Analytics services.