DAX Function to Get Maximum Value of Week by Year

Overview

This DAX function calculates the maximum value for each week, segmented by year. This can be particularly useful in financial reporting, sales analysis, and other data-driven decision-making processes where weekly performance needs to be evaluated over multiple years.

Function Definition

MaxValuePerWeekByYear = 
VAR MaxValueByWeek =
    ADDCOLUMNS(
        VALUES('Date'[Week]),
        "MaxValue", MAXX(
            FILTER(
                'Sales',
                'Sales'[Date] >= MIN('Date'[Date]) && 'Sales'[Date] <= MAX('Date'[Date])
            ),
            'Sales'[Amount]
        )
    )
RETURN
    SUMMARIZE(
        MaxValueByWeek,
        'Date'[Year],
        "Week", 'Date'[Week],
        "MaxValue", MAXX(MaxValueByWeek, [MaxValue])
    )

Function Breakdown

1. VAR MaxValueByWeek:

   • Creates a variable to hold the maximum value per week.
   • Uses ADDCOLUMNS to create a virtual table with unique weeks derived from the Date table.
   • Utilizes MAXX in combination with FILTER to calculate the maximum sales amount for the relevant week.

2. RETURN:

   • SUMMARIZE is used to group results by year and week, aggregating the maximum values into a new summarized table.

Input Validation

• Ensure the Sales and Date tables have relevant and correctly structured data.
• Check that the Amount field in the Sales table is numeric.

Error Handling

• The function assumes there is always data within the filtered context. If not, it may return an error or a blank result. Using IFERROR can be an option for handling such cases if incorporated.

Example Usage

Suppose you have a Sales table with columns: Date, Amount and a related Date table with columns: Year, Week, and Date. You can use the function above to create a new measure in Power BI that shows the maximum sales value per week for each year.

Example Scenario:

1. Create or load your Date and Sales tables in your Power BI model.
2. Add this function as a new measure in your Sales table.
3. Utilize the measure in a visual, such as a line chart, where you can display years on the X-axis and the maximum value on the Y-axis to analyze trends over time.

Conclusion

The above DAX code efficiently computes the maximum weekly sales value segmented by year, offering insights for analysis and decision-making. For those looking to deepen their understanding of DAX and its applications, consider exploring additional resources and courses on the Enterprise DNA Platform.

This solution exemplifies a scalable, efficient, and functional approach to solving the problem at hand in the realm of data analysis using Power BI.