DAX Function for Date Tagging

DAX Function to Tag Dates Based on Day of the Month

In this section, we will write a DAX function to tag dates based on the day of the month. Specifically, if the day of the month is less than 20, it will be tagged as 'MOC'. This code snippet will include necessary comments, proper documentation, and input validation checks to ensure robustness and clarity.

Necessary Imports

No specific imports are needed for DAX, as it is inherently integrated within Power BI, SSAS, or Excel's Power Pivot.

Function and Documentation

Function: TagDateAsMOC

This function checks whether the day of the month is less than 20 and tags it as 'MOC'.

-- TagDateAsMOC: Function to tag dates where the day of the month is less than 20 as 'MOC'
-- Params: 
-- [DateColumn]: Date column in the table to be evaluated.
-- Returns: 
-- A string 'MOC' if the condition is met, otherwise an empty string.

TagDateAsMOC = 
VAR ValidatedDate = 
    IF(
        ISBLANK([DateColumn]),
        ERROR("Date column cannot be blank."),
        [DateColumn]
    )
RETURN
    IF(
        DAY(ValidatedDate) < 20, 
        "MOC", 
        ""
    )

Commentary

1. Input Validation:

   • The function first checks if the date column is blank using the ISBLANK function. If the date is blank, it raises an error.

2. Logic and Tagging:

   • The core logic is encapsulated in the IF statement:
     • DAY(ValidatedDate) < 20 checks if the day of the month of the given date is less than 20.
     • If the condition is true, the function returns 'MOC'.
     • Otherwise, it returns an empty string.

Code Usage Example

Let's consider a sample table named Sales with a column OrderDate. Here’s how you can add a new column to tag dates based on the specified rule:

-- Adding a new calculated column to the 'Sales' table to tag dates
Calculated Columns:
Sales[DateTag] = 
    VAR ValidatedDate = 
        IF(
            ISBLANK(Sales[OrderDate]),
            ERROR("OrderDate cannot be blank."),
            Sales[OrderDate]
        )
    RETURN
        IF(
            DAY(ValidatedDate) < 20, 
            "MOC", 
            ""
        )

Practical Example

Imagine a scenario where you are working in Power BI with a Sales DataTable. You want to create a new column that tags orders made before the 20th day of each month as 'MOC'. The provided DAX logic can be added directly into Power BI:

1. Load your data into Power BI.
2. Navigate to Modeling -> New Column.
3. Enter the DAX expression as shown above in the "Calculated Columns" section.
4. Name the new column DateTag.

Conclusion

This function serves as a basic example of how you can use DAX to perform date-based conditional tagging. In practice, such tagging helps in segmenting your data for more nuanced analysis. If you wish to further advance your DAX skills, consider exploring resources from Enterprise DNA's Data Mentor platform. Happy analyzing!