Power BI Practice: Analyzing Sales Over Multiple Date Keys in Power BI Using Referenced Date Queries

Scenario:

You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys: 1. Due Date 2. Order Date 3. Delivery Date You need to support the analysis of sales over time based on all the date foreign keys.

Solution: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and Delivery Date. Does this meet the goal?

Answer: Yes, this solution meets the goal. By renaming the date query as Due Date and then referencing it twice to create the queries for Order Date and Delivery Date, you effectively create three separate date tables. This allows you to analyze sales over time based on all the date foreign keys (Due Date, Order Date, and Delivery Date) in your Power BI report.

Step-by-step Guide

Step 1: Open Power Query Editor

1. Open your Power BI Desktop file.

2. Click on the "Transform Data" button to open Power Query Editor.

Step 2: Rename the Date Query

1. In Power Query Editor, locate your date table query.

2. Right-click on the date table query and select "Rename."

3. Rename the query to "Due Date."

Step 3: Reference the Due Date Query

1. Right-click on the "Due Date" query.

2. Select "Reference" to create a new query that references the "Due Date" query.

3. Rename this new query to "Order Date."

Step 4: Create Another Reference for Delivery Date

1. Right-click on the "Due Date" query again.

2. Select "Reference" to create another new query that references the "Due Date" query.

3. Rename this new query to "Delivery Date."

Step 5: Load the Queries

1. Click on "Close & Apply" to load the queries back into Power BI.

Step 6: Create Relationships in the Data Model

1. Go to the "Model" view in Power BI Desktop.

2. Create relationships between the "Sales" table and each of the date tables:

• Drag the "Due Date" field from the "Sales" table to the "Date" field in the "Due Date" table.

• Drag the "Order Date" field from the "Sales" table to the "Date" field in the "Order Date" table.

• Drag the "Delivery Date" field from the "Sales" table to the "Date" field in the "Delivery Date" table.

Step 7: Use the Date Tables in Your Analysis

1. Now you can use the "Due Date," "Order Date," and "Delivery Date" tables in your visuals and calculations to analyze sales over time based on each of these date keys.

Sample Data

Sales Table

Date Table