Use the Events in Progress pattern to return the records that are active (i.e., between two dates) during a given time period. Use Inactive relationships between the 2 date fields in the Fact table and main field in the Dates table.
For example, you may want to know the number of sales orders that have been ordered but not shipped in a certain time period. So, first setup 2 Inactive relationships in your data model between:
- Sales[Order Date] → Dates[Date]
- Sales[Ship Date] → Dates[Date]
Using a date slicer, you can then calculate the Total Sales “in progress” during the selected period using the DAX formula:
To get the value of the orders in progress:
Sales in Progress =
// DAX PATTERN NAME: Events in Progress
// NOTES: Use 2 inactive relationships between the FACT table and the DATE table along with a FILTER for each DATE
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename ALL occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
SUMX(
Sales,
Sales[Quantity] * RELATED( Products[Unit Price] )
),
// replace [SUMX] calculation as necessary
FILTER(
VALUES( Sales[Order Date] ),
Sales[Order Date] <= MAX( Dates[Date] )
),
// replace Sales[Order Date] with the date representing the "start" of the "period"; replace Dates[Date] with the name of the main [Date] field in the [Dates] table
FILTER(
VALUES( Sales[Ship Date] ),
Sales[Ship Date] >= MAX( Dates[Date] )
) // replace Sales[Ship Date] with the date representing the "end" of the "period"; replace Dates[Date] with the name of the main [Date] field in the[Dates] table
)
(The first FILTER creates a table of sales that were ordered before the end of the selected period, while the second FILTER creates a table of the sales that have not yet been shipped as of the end of the selected date; those sales records that meet both criteria are the ones used in the SUM.)
NOTE: adjust the conditionals as necessary (e.g., “>= MAX” portion of “Sales[Ship Date] >= MAX( Dates[Date] )” to suit your definition of an active record (e.g., if an sale is only considered shipped after the ship date as opposed to on the ship date, change the “>=” to “>”, etc.).
Similarly, to get the number of orders in progress:
Orders in Progress =
// DAX PATTERN NAME: Events in Progress
// NOTES: Use 2 inactive relationships between the FACT table and the DATE table along with a FILTER for each DATE
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename ALL occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
COUNTROWS( Sales ),
FILTER(
VALUES( Sales[Order Date] ),
Sales[Order Date] <= MAX( Dates[Date] )
),
// replace Sales[Order Date] with the date representing the "start" of the "period"; replace Dates[Date] with the name of the main [Date] field in the [Dates] table
FILTER(
VALUES( Sales[Ship Date] ),
Sales[Ship Date] >= MAX( Dates[Date] )
) // replace Sales[Ship Date] with the date representing the "end" of the "period"; replace Dates[Date] with the name of the main [Date] field in the[Dates] table
)
NOTE: you’ll need to explicitly state the relationship to use in your measures via USERELATIONSHIP. For example, the [Total Sales] measure would be as follows:
Total Sales =
CALCULATE(
SUMX(
Sales,
Sales[Quantity] * RELATED( Products[Unit Price] )
),
USERELATIONSHIP( Dates[Date], Sales[Order Date] )
)
TIP: don’t use a column or measure that is outside of the main (fact) table; rather, use an aggregation (e.g., SUM) of a main (fact) table column or an iteration (e.g., SUMX) of related (lookup) table columns
