Customising Data Tables in Power BI
Date: 31 August, 2020
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!
Want to find out more? Contact an Acclario IT expert consultant about improving business productivity in real-time today.
Pro Tip- Accelerating Intranet setup with Acclario IT- Part 2 Date: 30 November, 2020 Let's Talk Intranets, Again Our digital transformation consultants over the last 18 months have collaborated and worked with many Queensland companies to install modern...
SharePoint Syntex from Project Cortex Date: 30 October, 2020Microsoft SharePoint Syntex- Let's break it down The Project Cortex teams’ have been driving innovation for over three years (September 2017) and since Ignite 2020 they have delivered. (((Project Cortex...
Pro Tip- Turn on Power BI Line Graph Forecasting Date: 30 October, 2020Let's Talk Power BI Select the line graph Select the Analytics button in the Visualisations Pane Scroll down the Forecast section Once there you can select + Add And you can adjust your...