DAX Function to Exclude Null Dates

This document outlines a DAX function that filters out records with null date values in a specified column. The function is designed for use within Power BI or any environment that supports DAX.

Function Overview

The aim of this function is to create a table that only contains rows where the date column is not null. This is useful for reports where valid date data is required for analysis and calculations.

Function Definition

ExcludingNullDates = 
VAR FilteredTable = 
    FILTER(
        YourTableName, 
        NOT(ISBLANK(YourTableName[YourDateColumn]))
    )
RETURN 
    FilteredTable

Documentation

ExcludingNullDates

• Purpose: Filters out records from YourTableName where YourDateColumn is null or blank.
• Parameters:
  • YourTableName: The name of the table you wish to filter.
  • YourDateColumn: The name of the column containing date values to check for nulls.
• Return Type: Table
• Exceptions: None. The function assumes valid table and column names.

Input Validation

• Ensure that YourTableName and YourDateColumn are valid identifiers within your data model.
• The function does not raise exceptions but may return an empty table if all date values are null.

Logic Breakdown

1. VAR FilteredTable: This variable holds the filtered result of the original table.
2. FILTER Function: Utilized to evaluate each row and retain only those where the date column is not blank.
3. ISBLANK Function: Checks if the date value is null or blank.
4. RETURN Statement: Outputs the filtered table.

Example Usage

This function can be directly utilized in a measure or as a calculated table. Here’s how you might implement it:

SalesWithoutNullDates = 
ExcludingNullDates(
    Sales,
    Sales[OrderDate]
)

In this example, Sales is the table containing sales data, and OrderDate is the date column that is checked for null values.

Conclusion

The ExcludingNullDates DAX function is an efficient way to filter out unwanted null dates from your data, ensuring that subsequent calculations and visualizations only use valid date information. For further learning on advanced DAX concepts and techniques, consider exploring courses available on the Enterprise DNA Platform.