Dynamic DAX Formula for Conditional Summation by Month

Objective

Create a DAX measure that dynamically calculates the sum of specific columns based on the selected month in a slicer, adhering to the following rules:

• If Month 4 is selected, sum M1 column for Month 4.
• If Month 5 is selected, sum M1 for Month 5 and M2 for Month 4.
• If Month 6 is selected, sum M1 for Month 6, M2 for Month 5, and M3 for Month 4.
• Continue this pattern for the fiscal year 2024.

Code Implementation

Dynamic_Sum_Measure = 
VAR SelectedMonth = MONTH(SELECTEDVALUE(Date[Date]))
VAR SelectedYear = YEAR(SELECTEDVALUE(Date[Date]))

RETURN
    SWITCH(
        TRUE(),
        SelectedMonth = 4, 
            CALCULATE(SUM(TableName[M1]), MONTH(TableName[Date]) = 4 && YEAR(TableName[Date]) = SelectedYear),
        SelectedMonth = 5, 
            CALCULATE(SUM(TableName[M1]), MONTH(TableName[Date]) = 5 && YEAR(TableName[Date]) = SelectedYear) +
            CALCULATE(SUM(TableName[M2]), MONTH(TableName[Date]) = 4 && YEAR(TableName[Date]) = SelectedYear),
        SelectedMonth = 6, 
            CALCULATE(SUM(TableName[M1]), MONTH(TableName[Date]) = 6 && YEAR(TableName[Date]) = SelectedYear) +
            CALCULATE(SUM(TableName[M2]), MONTH(TableName[Date]) = 5 && YEAR(TableName[Date]) = SelectedYear) +
            CALCULATE(SUM(TableName[M3]), MONTH(TableName[Date]) = 4 && YEAR(TableName[Date]) = SelectedYear),
        // Continue logic for each month...
        SelectedMonth = 7, 
            // Add logic for month 7
        // Add further cases as needed for the remaining months.
        
        // Default case - return 0 if no conditions are met
        BLANK()
    )

Explanation

1. Variables:

   • SelectedMonth: Retrieves the month number from the slicer's selected date.
   • SelectedYear: Extracts the year from the selected date to ensure data is filtered for the correct fiscal year.

2. SWITCH Statement: Evaluates which month is selected and accordingly sums up the appropriate columns:

   • Month 4: Sums M1 for Month 4.
   • Month 5: Sums M1 for Month 5 and M2 for Month 4.
   • Month 6: Sums M1, M2, and M3 as per the defined rules.
   • Additional cases for future months should be added similarly.

3. CALCULATE Function: Filters the data based on selected month and year, ensuring accurate context during calculations.

Usage Example

In a Power BI report:

1. Add the created measure Dynamic_Sum_Measure to your report.
2. Include a date slicer for users to select the month.
3. The measure will dynamically adjust its total based on the selected month, summing the respective columns accordingly.

Notes

• Ensure your data model includes a Date dimension table that allows proper time intelligence functions.
• Test the function with different slicer inputs to validate its behavior across the fiscal year.

Further Learning

For more advanced DAX techniques and best practices, I recommend exploring the Enterprise DNA Platform, which offers comprehensive resources tailored for improving your DAX proficiency.