Dynamic DAX Formula for Yearly Summation Based on Slicer Selection

This guide provides a DAX function that dynamically sums columns based on the selected month from a date slicer and additional filters like product name, channel name, smoker status, age, name, and sex. The code will account for specific summation logic for the Fiscal Year 2024.

Function Overview

Purpose

The function DynamicSum() calculates the total based on the selected month, summing M1, M2, and M3 columns as per the rules defined.

Parameters

• SelectedDate: The date selected by the user from the slicer.
• FilterTable: A table filtered based on slicer selections (product name, channel name, smoker status, age, name, sex).

Return Type

• Returns a numeric value representing the summed total.

Exceptions

• Will return 0 if no valid month is selected or if no data exists for the selected conditions.

DAX Code

DynamicSum = 
VAR SelectedMonth = MONTH(SELECTEDVALUE('Date'[Date]))
VAR Year = 2024
VAR TotalSum =
    SWITCH(
        TRUE(),
        SelectedMonth = 4, 
            CALCULATE(SUM('Table'[M1]), FilterTable),
        SelectedMonth = 5,
            CALCULATE(SUM('Table'[M1]) + SUM('Table'[M2]), FilterTable),
        SelectedMonth = 6,
            CALCULATE(SUM('Table'[M1]) + SUM('Table'[M2]) + SUM('Table'[M3]), FilterTable),
        SelectedMonth = 7,
            CALCULATE(SUM('Table'[M1]) + SUM('Table'[M2]) + SUM('Table'[M3]), FilterTable),
        // Additional months can be added similarly
        BLANK() // Return blank if no case matches
    )
RETURN
    TotalSum

Code Explanation

1. Variable Declaration:

   • SelectedMonth: Extracts the month from the date selected in the slicer.
   • Year: Fixed year for calculations (2024).

2. Calculation Logic:

   • The SWITCH(TRUE(), ...) construct allows for multiple conditional checks based on the selected month.
   • For each month from April to June, the code specifies how to sum the columns:
     • Month 4: Only sums M1.
     • Month 5: Sums M1 and M2.
     • Month 6: Sums M1, M2, and M3.
   • You can extend the logic for additional months as needed.
   • If none of the cases match, it returns BLANK().

Practical Usage Example

To utilize the DynamicSum measure, follow these steps:

1. Create a date table with a column called Date that includes the full range of dates for fiscal year 2024.

2. Add slicers to your report for the fields:

   • Date from your Date table.
   • Product Name, Channel Name, etc., from your filter table.

3. Insert a visual (e.g., Card, Table) and place the DynamicSum measure in it.

As users make selections in the slicer, the DynamicSum will update automatically to reflect the correct sum according to the pre-defined rules.

Conclusion

This DAX function effectively manages complex summation requirements with dynamic filtering capabilities. For further learning on DAX and advanced analytics, consider exploring the Enterprise DNA Platform, which offers a range of courses designed to enhance your data modeling and analytical skills.