DAX Function for Filtering Dates

This function allows you to create a slicer in Power BI where the date is either after a specified date or null. This approach enables users to filter data effectively based on date conditions.

Function Definition

Slicer Creation

FilteredDates = 
VAR SelectedDate = DATE(2023, 1, 1)  -- Change this to your desired date
RETURN
    FILTER (
        'YourDateTable',  -- Replace with your actual date table name
        'YourDateTable'[DateColumn] > SelectedDate || 'YourDateTable'[DateColumn] = BLANK()
    )

Documentation

FilteredDates

Purpose:
This DAX function creates a filtered table of dates from 'YourDateTable' 
where the dates are after a specified date or are null (BLANK).

Parameters:
- SelectedDate: The date threshold used for filtering. Adjust as needed.

Returns:
- A table containing dates that are greater than the SelectedDate 
  or that are blank.

Exceptions:
- Ensure 'YourDateTable' and 'DateColumn' exist in the data model.

Input Validation

• Ensure that the date table (YourDateTable) and the date column (DateColumn) are valid references.
• Adjust the SelectedDate variable to capture the desired threshold date, which should be a valid date.

Code Explanation

1. Variable Declaration:

   • SelectedDate is a variable where you can define your cutoff date. Adjust as necessary.

2. Filter Function:

   • FILTER applies conditions to YourDateTable, returning rows where the [DateColumn] is greater than SelectedDate or is blank.

Code Usage Example

You can use this DAX function in Power BI by creating a new measure or calculated table. Here’s how it could be utilized:

1. Create a new calculated table:

   FilteredDateTable = 
   VAR SelectedDate = DATE(2023, 1, 1)  -- Desired cut-off date
   RETURN
       FILTER (
           'DateTable',  -- Name of your date table
           'DateTable'[Date] > SelectedDate || 'DateTable'[Date] = BLANK()
       )

2. This filtered table can then be used as a slicer in your reports, allowing users to dynamically select a cutoff date.

Conclusion

This DAX function provides a flexible way to filter dates based on whether they are after a specific date or are null, enhancing the usability of date slicers in Power BI dashboards. For advanced DAX training and further insights, consider exploring courses from the Enterprise DNA Platform.