DAX Function to Get Maximum of Week with Sum of Days

This DAX function calculates the maximum sum of days grouped by week from a specified date column in a dataset. It aggregates the number of days and returns the maximum value across all weeks.

Overview

• Purpose: To compute the maximum sum of days for each week from a given date column.
• Inputs: A date column and a numeric column representing the days to be summed.
• Output: The maximum sum of days across all weeks.

Code Implementation

MaxSumOfDaysPerWeek = 
VAR DaysTable = 
    SUMMARIZE(
        'YourTableName',                       // Change 'YourTableName' to your actual table name
        'YourTableName'[Week],                 // Assume 'Week' as a column representing week numbers
        "TotalDays", SUM('YourTableName'[Days]) // Assuming 'Days' is the numeric column to sum
    )
VAR MaxSummedDays = 
    MAXX(
        DaysTable, 
        [TotalDays]
    )
RETURN 
    MaxSummedDays

Explanation of Components

1. SUMMARIZE Function:

   • Groups the data by week and calculates the total days for each week.
   • DaysTable stores this aggregation.

2. MAXX Function:

   • Iterates over the DaysTable to find the maximum value of TotalDays.
   • Returns this maximum value.

3. RETURN Statement:

   • Outputs the maximum sum of days for the weeks calculated.

Input Validation

While DAX does not directly support complex input validation like other programming languages, it's assumed that:

• 'YourTableName' and 'Days' columns exist and contain appropriate data types (dates and numbers).

Usage Example

Assuming you have a table called SalesData with a numeric column Days indicating the number of days in a week and a Week column indicating the week number:

MaxSumOfWeeks = 
MaxSumOfDaysPerWeek 

This measure can be added to your Power BI dashboard to visualize the maximum sum of days aggregated by weeks from the SalesData table.

Conclusion

This MaxSumOfDaysPerWeek function efficiently computes the maximum sum of days per week while adhering to best practices in DAX coding. For further enhancement of your DAX skills, consider exploring the Enterprise DNA platform for advanced training and resources.