Power BI Practice: Analyzing Sales Over Time in Power BI Using Inactive Relationships for Multiple Date Keys
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:
Due Date
Order Date
Delivery Date You need to support the analysis of sales over time based on all the date foreign keys.
Solution: For each date foreign key, you add inactive relationships between the sales table and the date table. Does this meet the goal?
Answer: No, this solution does not fully meet the goal. Adding inactive relationships between the sales table and the date table for each date foreign key allows you to create multiple relationships, but it won't automatically support analysis over time based on all the date foreign keys.
To achieve the goal, you should:
Create inactive relationships between the sales table and the date table for each date foreign key.
Use DAX functions like USERELATIONSHIP to activate the appropriate relationship in your measures or calculations when needed.
This way, you can dynamically switch between the different date relationships in your analysis.
Practical Exercise:
Analyzing Sales Over Time with Multiple Date Foreign Keys Objective To practice creating and using inactive relationships in Power BI to analyze sales data over time based on different date foreign keys.
Steps
- Open Power BI Desktop
• Launch Power BI Desktop on your computer.
- Load Data
• Click on "Get Data" and select "Azure SQL Database".
• Connect to your Azure SQL Database and load the Sales table and the Date table.
- Create Relationships
• Go to the "Model" view.
• Create relationships between the Sales table and the Date table:
• Due Date: Create an inactive relationship between Sales[DueDate] and Date[Date].
• Order Date: Create an inactive relationship between Sales[OrderDate] and Date[Date].
• Delivery Date: Create an inactive relationship between Sales[DeliveryDate] and Date[Date].
- Create Measures Using USERELATIONSHIP
• Go to the "Data" view.
• Create measures to use the inactive relationships. For example:
Total Sales by Due Date = CALCULATE( SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[DueDate], Date[Date]) )
Total Sales by Order Date = CALCULATE( SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[OrderDate], Date[Date]) )
Total Sales by Delivery Date = CALCULATE( SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]) )
- Create Visualizations
• Go to the "Report" view.
• Create visualizations to analyze sales over time using the measures you created.
• For example, create line charts to show Total Sales by Due Date, Total Sales by Order Date, and Total Sales by Delivery Date.
- Verify the Analysis
• Ensure that the visualizations correctly reflect the sales data over time based on the different date foreign keys.
- Document the Process
• Write a brief report documenting the steps you took to create and use the inactive relationships.
• Include screenshots of key steps, such as creating relationships and measures.
- Expected Outcome
You should be able to analyze sales data over time based on different date foreign keys by using measures that activate the appropriate relationships.
Sample Table
A sample table you can use to practice the steps for analyzing sales over time with multiple date foreign keys:
Sales Table
| SalesID | DueDate | OrderDate | DeliveryDate | Product | Quantity | SalesAmount |
| 1 | 2024-01-15 | 2024-01-10 | 2024-01-20 | Laptop | 2 | 1500 |
| 2 | 2024-02-10 | 2024-02-05 | 2024-02-15 | Mouse | 5 | 100 |
| 3 | 2024-03-20 | 2024-03-15 | 2024-03-25 | Keyboard | 3 | 300 |
| 4 | 2024-04-25 | 2024-04-20 | 2024-04-30 | Monitor | 1 | 200 |
| 5 | 2024-05-30 | 2024-05-25 | 2024-06-04 | Printer | 2 | 400 |
| 6 | 2024-06-15 | 2024-06-10 | 2024-06-20 | Tablet | 4 | 800 |
| 7 | 2024-01-15 | 2024-01-10 | 2024-01-20 | Laptop | 2 | 1500 |
| 8 | 2024-02-10 | 2024-02-05 | 2024-02-15 | Mouse | 5 | 100 |
| 9 | 2024-03-20 | 2024-03-15 | 2024-03-25 | Keyboard | 3 | 300 |
| 10 | 2024-04-25 | 2024-04-20 | 2024-04-30 | Monitor | 1 | 200 |
| 11 | 2024-05-30 | 2024-05-25 | 2024-06-04 | Printer | 2 | 400 |
| 12 | 2024-06-15 | 2024-06-10 | 2024-06-20 | Tablet | 4 | 800 |
| 13 | 2024-01-15 | 2024-01-10 | 2024-01-20 | Laptop | 2 | 1500 |
| 14 | 2024-02-10 | 2024-02-05 | 2024-02-15 | Mouse | 5 | 100 |
| 15 | 2024-03-20 | 2024-03-15 | 2024-03-25 | Keyboard | 3 | 300 |
| 16 | 2024-04-25 | 2024-04-20 | 2024-04-30 | Monitor | 1 | 200 |
| 17 | 2024-05-30 | 2024-05-25 | 2024-06-04 | Printer | 2 | 400 |
| 18 | 2024-06-15 | 2024-06-10 | 2024-06-20 | Tablet | 4 | 800 |
A DAX expression to generate a date table in Power BI:
DateTable =
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 6, 30 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"MonthName", FORMAT ( [Date], "MMMM" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Weekday", WEEKDAY ( [Date], 2 ),
"WeekdayName", FORMAT ( [Date], "dddd" )
)
You can use these tables to follow the steps in the practical exercise and create inactive relationships between the Sales table and the Date table for the DueDate, OrderDate, and DeliveryDate columns. Then, use DAX functions to activate these relationships in your measures.