DAX Function for Calculating Handlers

Overview

The following DAX function named Handlers calculates the number of buyer customers for a defined date range, specifically looking at a 3-month period leading up to the last completed month. It handles different conditions depending on whether the selected month is the minimum date available.

Code Implementation

Handlers = 
VAR _MIN = EDATE( MAX( dly_trans_snp[Start Of Month] ), -3 )  -- The first day, 3 months ago
VAR _MAX = MAX( dly_trans_snp[Start Of Month] ) - 1            -- Last day from last month (closed month)
VAR _MIN_ALL = CALCULATE( MIN( dly_trans_snp[Start Of Month] ), ALLSELECTED( dly_trans_snp ) ) -- Min date for base
RETURN
    IF(
        SELECTEDVALUE( dly_trans_snp[Start Of Month] ) = _MIN_ALL, 
        [# Buyers Customers],   -- Return total buyers if current month is the minimum
        CALCULATE(
            [# Buyers Customers],
            FILTER(
                ALLSELECTED(dly_trans_snp),
                dly_trans_snp[date] >= _MIN &&  -- Filter for dates greater than or equal to _MIN
                dly_trans_snp[date] <= _MAX     -- and less than or equal to _MAX 
            )
        )
    )

Explanation of the Code

1. Variables Definition:

   • _MIN: This variable identifies the starting point for the 3-month range before the selected month using the EDATE function.
   • _MAX: Represents the last date of the prior full month, which is derived by subtracting one from the maximum date in the dataset.
   • _MIN_ALL: Calculates the minimum date across all selected data, ensuring that the function can handle contexts appropriately.

2. Logic Flow:

   • The IF statement checks if the current selected month is the minimum date.
     • If true, it directly returns the total count of buyers ([# Buyers Customers]).
     • If false, it calculates the count of buyers within the specified date range using the CALCULATE and FILTER functions.

3. Context Handling:

   • ALLSELECTED: This function adjusts the context to include all selected dates while filtering the data according to the defined range.

Usage Example

Imagine you want to analyze how customer purchases have changed over the past three months relative to the end of the previous month. Assuming you have a measure [# Buyers Customers] that counts the number of unique buyers, you can incorporate the Handlers function into your dashboard or report.

For instance, when users select a specific month in a slicer, the visual displaying the number of buyers will automatically update based on the logic defined in the Handlers function, granting them insights into buying patterns over time.

Additional Considerations

• Ensure that your data model has all the necessary relationships and that the column dly_trans_snp[date] includes appropriate date values.
• Before implementing this function, validate that the measure [ # Buyers Customers] is accurately defined within your model.

For further learning and enhancement of your DAX skills, consider exploring courses available on the Enterprise DNA Platform.