DAX Function to Calculate Days for a Given Year

This document provides a DAX function that calculates the total number of days in a specific year, accounting for leap years. The function is robust, efficient, and follows best practices in DAX coding.

Function Definition

Purpose

The function calculates the total number of days in a specified year.

Code Snippet

Here's the DAX code to implement the function:

TotalDaysInYear = 
VAR SelectedYear = SELECTEDVALUE(YourDateTable[Year])
RETURN
    IF(
        ISBLANK(SelectedYear),
        BLANK(),
        IF(
            MOD(SelectedYear, 4) = 0 && (MOD(SelectedYear, 100) <> 0 || MOD(SelectedYear, 400) = 0),
            366,  // Leap year
            365   // Regular year
        )
    )

Explanation of the Code

1. Variable Declaration:

   • SelectedYear: Retrieves the selected year from the YourDateTable. Ensure that this corresponds to your actual date table column.

2. Checking for Blank Values:

   • The function first checks if SelectedYear is blank. If it is, it returns BLANK().

3. Leap Year Calculation:

   • The function uses conditional logic to determine if the year is a leap year:
     • A year is a leap year if it is divisible by 4.
     • It is not a leap year if it is divisible by 100, unless it is also divisible by 400.
   • If a leap year is detected, it returns 366, otherwise 365.

Input Validation

• The function uses SELECTEDVALUE to ensure it operates on a single value from the year column.
• It gracefully handles cases where no year is selected by returning BLANK().

Code Usage Example

To use the TotalDaysInYear measure, ensure you have a date table in your data model with a column for years. You can then visualize this measure in a report or use it in another calculation.

For instance, if you have a slicer for years in your report:

• Add the TotalDaysInYear measure to a card visualization. This will display the total days for the selected year based on user input.

Conclusion

This DAX function provides an effective way to calculate the number of days in a year, considering leap years. You can leverage this function within your reports and dashboards to enhance data insights.

For further learning on writing advanced DAX functions, consider exploring the courses offered on the Enterprise DNA Platform.