Calculation of the first transaction date and last transaction date per customer dynamically based on the report context requires the DAX functions MIN, MAX, CALCULATE, and RELATED TABLE. This ensures that the measure responds to the filters applied in the report.
Methodology: Using CALCULATE, MIN, and MAX
Create Measures for the First and Last Transaction Date:
DAX is used to create two measures for the first and last transaction dates:
First Transaction Date =
CALCULATE(
MIN(Sales[Transaction Date]),
ALLEXCEPT(Sales, Sales[Customer ID])
)
Last Transaction Date =
CALCULATE(
MAX(Sales[Transaction Date]),
ALLEXCEPT(Sales, Sales[Customer ID])
)
Explanation of the Measures:
-
CALCULATE: Changes the context to focus on a specific customer.
-
MIN and MAX: Find the first and last dates within the filtered context.
-
ALLEXCEPT: Preserves filtering on the Customer ID column while ignoring others.
-
The measures respond dynamically to filters like slicers or visual-level filters.
Alternative Approach: Using Variables for Clarity
For better readability, use variables in a single measure:
Transaction Date Range =
VAR FirstDate =
CALCULATE(
MIN(Sales[Transaction Date]),
ALLEXCEPT(Sales, Sales[Customer ID])
)
VAR LastDate =
CALCULATE(
MAX(Sales[Transaction Date]),
ALLEXCEPT(Sales, Sales[Customer ID])
)
RETURN
"First Transaction: " & FORMAT(FirstDate, "dd-mmm-yyyy") &
" | Last Transaction: " & FORMAT(LastDate, "dd-mmm-yyyy")
-
This measure returns a formatted string displaying both dates.
-
It remains dynamic, reflecting changes in filters.
Dynamic Filtering with Slicers (Advanced Option)
If your model involves multiple date tables or complex filtering, consider using USERELATIONSHIP or custom date tables to ensure accurate results.