Concept Breakdown
A custom date table is crucial for robust time intelligence calculations in Power BI. It provides a single, consistent source of date information, allowing for complex analyses across various date dimensions (Year, Month, Week, Day). By marking it as a date table and setting correct relationships and sorting, you empower DAX time intelligence functions and ensure accurate visual filtering, overcoming limitations of Power BI's automatic date/time feature.
What You Will Learn
Understand when to use Date Table Setup & Configuration in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a intermediate-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided file, then open the 'Music Tours - Custom Calendars.pbix' file in Power BI Desktop.
Import the 'Show calendar' worksheet from the 'Show calendar.xlsx' Excel file.
Mark the newly imported 'Show calendar' table as a date table, using its 'Date' column as the key.
Set the default summarization for any numeric columns in the 'Show calendar' table (e.g., 'Day', 'Month number', 'Weekday number') to 'Don't summarize'.
Adjust the 'Sort by column' setting for the following columns in the 'Show calendar' table:
- 'Month' should be sorted by 'Month number'.
- 'Weekday' should be sorted by 'Weekday number'.
Create a hierarchy in the Model view starting with the 'Decade' column. Rename this hierarchy to 'Show date hierarchy' and add 'Year', 'Month', 'Weekday', and 'Date' to it.
Hide the columns used for sorting ('Month number', 'Weekday number') and any columns that are also part of the 'Show date hierarchy' from the report view.
Establish a one-to-many relationship between the 'Date' column in the 'Show calendar' table and the 'Date' column in the 'Show' table.
Utilize the new calendar table and existing measures from the 'Music Measures' table to create relevant chart visuals.
Add a slicer visual to your report, using the 'Weekday' field from your custom date table, to filter the charts interactively.
Save and close the report.
Expected Outcome
A Power BI report with interactive chart visuals that correctly filter and display data based on a custom date table. The visuals should respond accurately to date hierarchy selections and the 'Weekday' slicer, demonstrating proper time intelligence and sorting.