Every Power BI file should have a dedicated and marked [Dates] table.
Goals
• create a dedicated [Dates] table
Tasks
Produce a Power BI file that:
• creates a [Sales] fact table from the sales data provided
• creates a [Dates] table with the following characteristics:
- uses a function created from the Enterprise DNA Extended Date Table code (both link and code provided)
- uses parameters to determine the earliest and latest order dates in the sales data
- uses parameters to determine the final date range (full years covering the earliest and latest order dates in the sales data)
- (the end date should be 1 year later to allow for forecasts)
• creates a [Key Measures] table to house the DAX measures created from the DAX data provided
• links the [Dates] and [Sales] tables into a star schema data model
• includes slicers, a table, a matrix, and cards to showcase the [Dates] table “in action” (i.e., while making slicer selections)
Notes
• use only the DAX code provided; do not modify nor create additional measures or calculated columns
• (data source: a 10,000 row subset of the Microsoft [ContosoRetailDW] sample database)
Submission
Using the only the sales data, dates table M code, and DAX files provided, create your solution, and reply to this post. Upload the following:
a screenshot of the queries pane of the [Power Query Editor] with all groups expanded
a screenshot of the star schema data model as displayed in the [Model View]
an animated GIF of your Power BI file “in action” (i.e., while making slicer selections)
your PBIX file
Please blur your screenshots and GIFs or place them in a hidden section.
use Query Editor in Power BI Desktop to do the following:
create a new blank query, paste the M code (as copied above), rename the blank query to [fxDatesQuery]
move [fxDatesQuery] into a new group [Functions]
create a new blank query for a parameter to hold the earliest fact table data date (use placeholder date of, say, 2020-01-01); rename the blank query to [pStartDataDate]
create a new blank query for a parameter to hold the latest fact table data date (use placeholder date of, say, 2020-12-31); rename the blank query to [pEndDataDate]
create a new blank query for a parameter to hold January 1st of the year of the earliest fact table data date; rename the blank query to [pStartDate]
create a new blank query for a parameter to hold December 31st of the year of the latest fact table data date (add 1 year to ensure DAX code that uses time intelligence functions operates properly); rename the blank query to [pEndDate]
move the [pStartDataDate], [pEndDataDate], [pStartDate], and [pEndDate] parameters into a new group [Parameters]
modify the code of the [fxDatesQuery] function to use the [pStartDate] and [pEndDate] parameters
Pre-Modelling Activities - Data Loading:
load raw sales data from [Sales] tab of the Data Excel file; rename query to [RAW Sales]; deselect “Enable Load”; move into a new group [Staging Queries]
create reference of [RAW Sales]; rename as [Sales]; move into a new group [Data Model]
modify the placeholder dates in the [pStartDate] and [pEndDate] parameters to use the earliest and latest [Order Date] of the [Sales] table
use the [fxDatesQuery] function to create a new table; rename the resultant table to [Dates];
move the [Dates] table into the [Data Model] group
delete any relationships that may already exist (i.e., were automatically created by Power BI)
rearrange tables into waterfall layout, so that dimension (lookup) tables are in the top row, fact tables are in the bottom row, and measure tables are in columns at the right (so [Dates] on top, [Sales] below, and [Key Measures] at right)
use [Manage Relationships] to add (1-to many) relationships for:
create a new measure in the [Key Measures] table for each entry in the DAX Measures text file
Post-Modelling Activities - Visualizations:
create slicers for Sales[Country], Sales[Colour] and Dates[Date] columns
create a table for the Dates[Year] column and the [Current Year Sales] and [Previous Year Sales] measures
create a matrix with the Dates[Year] and Dates[Quarter] columns on rows and the [Current Quarter Sales] and [Previous Quarter Sales] measures on values
Thanks @Greg for the detailed solution. I’ve been using M code to load the Date table for a while but this is the first time I know about using parameters to determine the date range. I’ve learned a lot and look forward to another next workout.
Hi @collier.brittany. No problem. Just do the same thing many times; create a 1-to-many relationship between the [Dates] table and the fact table. WRT the setting of the parameters (as presented in this workout), you’d then need to adjust [pStartDataDate] and [pEndDataDate] to cover all fact tables in your model.
Can you please elaborate on
" * modify the code of the [fxDatesQuery] function to use the [pStartDate] and [pEndDate] parameters"
I am not getting how to use these 2 parameters in the fxDAtesQuery .
Hi @rishug2811. Absolutely you can … as this workout was in reference to the dates table, in trying to keep everything else as similar as possible I used the same pattern for the current and previous DAX measures. Greg
