DAX Formula for Counting Active Contracts

Objective

To create a DAX formula that counts the number of active contracts from the All_Contracts table based on the Colleague Name column while considering the dynamic filtering of the start and end dates of the contracts.

DAX Formula

The following formula counts the distinct colleague names associated with active contracts based on specified date conditions.

ActiveContractsCount = 
CALCULATE(
    DISTINCTCOUNT(All_Contracts[Colleague Name]),
    All_Contracts[Start Date] <= MAX(DateTable[Date]),
    All_Contracts[End Date] >= MIN(DateTable[Date])
)

Explanation of the Formula Components

1. CALCULATE Function:

   • This function modifies the context in which data is evaluated. It allows for the application of filters to the data being counted.

2. DISTINCTCOUNT Function:

   • This function counts the unique occurrences of Colleague Name within the rows that meet the specified conditions.

3. Filter Conditions:

   • All_Contracts[Start Date] <= MAX(DateTable[Date]): This condition checks if the start date of the contract is less than or equal to the highest date in the current filter context (e.g., if filtered by week/month).
   • All_Contracts[End Date] >= MIN(DateTable[Date]): This condition checks if the end date of the contract is greater than or equal to the lowest date in the current filter context, ensuring that the contract is still active.

Practical Example

Use Case

Assume you have a DateTable that is connected to your report allowing users to filter data by weeks, months, or specific dates. Your All_Contracts table has the following data:

Example Usage

If you have applied a filter in your report to show the active contracts for the month of June 2023, the formula would dynamically evaluate the distinct count of colleagues based on their active status during this time frame. For instance, John Doe and Bob Johnson would be counted as active as their contracts are valid during June.

Conclusion

The DAX formula provided allows for a dynamic count of active contracts filtered by date ranges, ensuring accurate and real-time data representation based on user selections in your reports. Please consider enhancing your DAX skills through the Enterprise DNA Platform for more complex scenarios and advanced analytics.