You have a Date Dimension in Power BI, making it easy to answer questions like:
- How many items did I ship last week?
- What was the total of invoices last month?
These are events that happened on one specific day. That day is linked to your Date Dimension.
There is a second type of date question reporting over a time span. For example, a Worker has a Start Date and End Date for their employment. Reporting on that time span is trickier.
- How many workers were employed at any point last month?
- What is the trend of total open opportunities by week?
Below are some DAX formula and Data modeling techniques for reporting across these date ranges.
Effective Date Dimension
To an end user, an effective date dimension looks identical to a regular date dimension.
The effective date dimension does not have Active Relationships to other tables. It either has no relationships or Inactive Relationships. Any logic controlling the dates is done inside the DAX expression.
Effective Date Range DAX Patterns
It’s helpful to keep an example in mind for these patterns. Consider a CRM Opportunity with the following Attributes:
- Created Date – Date the Opportunity was first opened (Jul 3, 2022).
- Closed Date – Date the Opportunity was won or lost (Sep 9, 2022).
- Open Dates – The range of dates when the Opportunity was active (Jul 3, 2022, to Sep 9, 2022)
An effective date concept is required to answer this business question:
How many opportunities were Open by month?
Below is a formula to count the opportunities and calculate if the opportunity was open in each month.
Count of Open Opportunities as of Date =
CALCULATE (
[Count of Opportunities],
FILTER (
Opportunities,
Opportunities[Created Date] <= MAX ( EffectiveDate[Date] )
&& (
Opportunities[Closed Date] >= MIN ( EffectiveDate[Date] )
|| Opportunities[Closed Date] = BLANK ()
)
)
)For our example opportunity, this calculation returns a count of one open opportunity for these date ranges:
- 2022
- Q3 2022
- Jul, Aug, Sep 2022
Breaking Down the Filter
Was the opportunity created on or before the effective date?
Opportunities[Created Date] <= MAX ( EffectiveDate[Date] )Was the opportunity closed on or after the effective date?
Opportunities[Closed Date] >= MIN ( EffectiveDate[Date] )Closed or End dates must be handled carefully. Extra code is required when active records have a null End Date. This means the record is currently active and should be included.
Opportunities[Closed Date] = BLANK ()In Dynamics 365 HR, open records have Effective End Dates of “12/31/2154”, making this extra condition unnecessary.
MIN and MAX Date
<= MAX and >= MIN is a common pattern. This pattern evaluates if the record was active during any part of the date range.
Another common pattern is <= MAX and > MAX. This pattern evaluates active records during the entire date range.
The MIN and MAX functions are critical for two reasons:
- Correctly applied MIN and MAX date conditions allow selection of any period (week, month, quarter, year) from the Effective Date Dimension.
- There are multiple “correct” patterns to use depending on the desired logic. Don’t assume the example above is the definitive pattern. If an Opportunity was closed during the Effective period, should it be included or excluded from the count?
⚠️ MIN/MAX and >, <, >=, <= are easy to mess up! Walk through the DAX logic and test report output with specific examples!
💡 Because DAX is similar to Excel Functions, it’s helpful to test date scenarios in a spreadsheet. Then apply the same code in Power BI.
USERELATIONSHIP()
A challenge in CRM or HR Data Models is determining the number of Date Dimensions to use. Sometimes multiple Date Dimensions are simpler to understand. For CRM and HR reporting, I prefer having a single Effective Date Dimension.
The USERELATIONSHIP DAX expression allows selecting the right relationship to use for a particular calculation. These are dates where the calculation isn’t reporting on a date range; it’s reporting on something that happened on a specific day.
Inactive Relationships
Date relationships are defined, but they are all inactive. Having them all inactive is important – it simplifies the DAX when writing Effective Date calculations.
USERELATIONSHIP Example
The CALCULATE DAX function requires one extra line of code to select the relationship.
How many Opportunities were Created in a given month?
Count of Created Opportunities =
CALCULATE (
[Count of Opportunities],
USERELATIONSHIP ( Opportunities[Created Date], EffectiveDate[Date] )
)This DAX expression is the same as having [Count of Opportunities] and an Active Relationship between the Created Date and Effective Date.
Effective Dates and Counts
CRM and HR reporting gets into unique scenarios around how a “count” is performed. It’s important to select the correct count function and the appropriate column.
- COUNTROWS ( Table ) – Number of rows in this table.
- COUNT ( Table[Column] ) – Number of non-blank values in this column.
- DISTINCTCOUNT ( Table[Column] ) – Number of unique values in this column.
How many employees did I hire this month?
It’s a simple question, but the surrounding data is not simple. Workers can go on leave, be transferred, and get rehired. The logic needs to be precise.
How many customers have open opportunities?
In this example, a distinct count is important along with the Effective date logic. The measure should include a distinct count of Account Id, while the filter is on opportunities.
Example Files
Download a Dynamics 365 CRM Data Model from GitHub to explore some of these calculations.
Joel Leichty 