Concept Breakdown
When a fact table contains multiple date columns that need to be analyzed independently, standard Power BI modeling only allows one active relationship between a fact table and a single date dimension. To enable comparisons or analysis based on other date columns, you can add additional date dimension tables. These additional date tables can then be related to the fact table with active relationships, allowing different report visuals to be filtered by different date contexts. While a single date dimension with inactive relationships and the USERELATIONSHIP function is an alternative, using multiple active calendar tables simplifies visual interaction when comparing distinct date types.
What You Will Learn
Understand when to use Data Modeling for Date Comparison 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 exercise file, which contains the 'Expenses' and 'Calendar' tables.
Import both tables from the unzipped Excel workbook into Power BI Desktop.
Identify and delete the incorrect automatic relationship that Power BI may create between the 'Expenses' and 'Calendar' tables (likely on an 'id' field).
Import a second copy of the 'Calendar' table from the same Excel workbook.
Rename the tables for clarity: 'Expenses' (for the fact table), 'Calendar Incurred' (for the calendar linked to incurred dates), and 'Calendar Paid' (for the calendar linked to paid dates).
Create an active relationship between the 'Expenses'[Incurred Date] column and the 'Calendar Incurred'[Date] column.
Create a second active relationship between the 'Expenses'[Paid Date] column and the 'Calendar Paid'[Date] column.
Create a basic measure 'Total Expenses' using the starter code provided.
Build a report visual, such as a matrix, using 'Year' from 'Calendar Incurred' on rows, 'Year' from 'Calendar Paid' on columns, and the 'Total Expenses' measure in values, to show expenses incurred in one year but paid in another.
Optionally, apply conditional formatting to highlight specific cells (e.g., amounts less than 100) to emphasize the differences.
Starter DAX
Total Expenses = SUM( 'Expenses'[Amount] )Expected Outcome
A report visual (e.g., a matrix or table) displaying expense amounts, cross-referenced by both the 'Incurred Year' and 'Paid Year'. This visual will clearly show instances where expenses incurred in one year (e.g., 2019) were paid in a subsequent year (e.g., 2020), with specific values highlighted by conditional formatting.