DAX Function to Generate a New Date Column

This document outlines a DAX function that creates a new date column by adding a specified number of days to the current date for each row in a table. This operation is useful for creating future dates based on current data.

Function Explanation

Purpose

The function will create a new calculated column in a table that adds a specified number of days to the current (or a specified) date.

Parameters

• Input Table: The table from which the current date will be taken.
• Days to Add: An integer value representing the number of days to add to the current date.

Return Type

• Returns a new date type column in the specified table.

Exceptions

• Raises an error if the input for "Days to Add" is not a valid integer.

DAX Code

The following DAX code defines a calculated column in a table (let's assume the table is named YourTable and the integer column is DaysToAdd).

NewDateColumn = 
    VAR CurrentDate = TODAY()  // Retrieve the current date
    VAR DaysToAdd = 'YourTable'[DaysToAdd]  // Get the value from the DaysToAdd column
    RETURN 
        IF(
            ISERROR(DaysToAdd),  // Validate if DaysToAdd is an error
            BLANK(),  // If there is an error, return a blank value
            CurrentDate + DaysToAdd  // Return the date added to the specified days
        )

Code Explanation

1. CurrentDate Variable:

   • Uses the TODAY() function to capture today’s date.

2. DaysToAdd Variable:

   • References the DaysToAdd column in YourTable.

3. IF Statement:

   • Checks if DaysToAdd is an error using ISERROR().
   • If true, it returns a BLANK, preventing any calculations on invalid inputs.
   • If valid, it adds DaysToAdd to CurrentDate to generate the new date.

Example Usage

Suppose you have a table named YourTable with the following data:

After applying the DAX calculated column formula, the table will look like this:

Conclusion

This DAX function effectively allows users to create a new date column based on a dynamic addition of days calculated from the current date. It demonstrates efficient handling of potential errors and adheres to best practices in DAX coding. For advanced DAX training, consider exploring courses on the Enterprise DNA Platform.