Code Generator | DAX

Dynamic DAX Conditional Summation Guide

This guide provides a DAX formula to dynamically sum sales data based on selected dates and apply filters like product name and smoker status, adapting the logic to the chosen month in a slicer, specifically for FY 2024.

Prompt

i need a dynamic dax formula such that , when a slicer is added with date, product name, channel name. it need to sumif such that if date selected is having month 4, it should filter and sum column M1 for month 4, if month 5 , it sum column M1 for for month 5 , column M2 for month 4, if selected month 6 date , it sum column M1 for month 6, column M2 for month 5 column M3 for month 4.. similary it should go for the entire FY 24. also I need dynamic filters such as smoker status, age, name, sex etc

Answer

Dynamic DAX Formula for Conditional Summation

This guide outlines a DAX formula to dynamically sum values based on selected dates and apply various filters like product name, channel name, smoker status, age, name, and sex. The formula adjusts summation logic according to the month selected in a slicer.

Requirements

Dynamic Date Selection: Summation needs to depend on the selected month.
Multiple Columns: Sum different M columns based on the month context.
Dynamic Filters: Consider additional filters from slicers (smoker status, age, etc.).

DAX Formula

Here’s how to structure the DAX formula to achieve the desired outcome:

DynamicSum = 
VAR SelectedDate = MAX('DateTable'[Date])                    -- Get the latest selected date
VAR SelectedMonth = MONTH(SelectedDate)                       -- Extract the month from the selected date
VAR SelectedYear = YEAR(SelectedDate)                         -- Extract the year to ensure it’s in FY24
VAR IsFY24 = SelectedYear = 2024                              -- Check if the year is 2024

RETURN
IF(
    IsFY24,
    SWITCH(
        TRUE(),
        SelectedMonth = 4, 
            CALCULATE(SUM(Sales[M1]), 'DateTable'[Month] = 4,
                            FILTER(ALLSELECTED(Sales), 
                                    [Smoker Status Filter] && 
                                    [Age Filter] &&
                                    [Name Filter] &&
                                    [Sex Filter])
            ),
        SelectedMonth = 5, 
            CALCULATE(SUM(Sales[M1]), 'DateTable'[Month] = 5, 
                            FILTER(ALLSELECTED(Sales), 
                                    [Smoker Status Filter] && 
                                    [Age Filter] &&
                                    [Name Filter] &&
                                    [Sex Filter])
            ) +
            CALCULATE(SUM(Sales[M2]), 'DateTable'[Month] = 4,
                            FILTER(ALLSELECTED(Sales), 
                                    [Smoker Status Filter] && 
                                    [Age Filter] &&
                                    [Name Filter] &&
                                    [Sex Filter])
            ),
        SelectedMonth = 6,
            CALCULATE(SUM(Sales[M1]), 'DateTable'[Month] = 6, 
                            FILTER(ALLSELECTED(Sales), 
                                    [Smoker Status Filter] && 
                                    [Age Filter] &&
                                    [Name Filter] &&
                                    [Sex Filter])
            ) +
            CALCULATE(SUM(Sales[M2]), 'DateTable'[Month] = 5, 
                            FILTER(ALLSELECTED(Sales), 
                                    [Smoker Status Filter] && 
                                    [Age Filter] &&
                                    [Name Filter] &&
                                    [Sex Filter])
            ) +
            CALCULATE(SUM(Sales[M3]), 'DateTable'[Month] = 4, 
                            FILTER(ALLSELECTED(Sales), 
                                    [Smoker Status Filter] && 
                                    [Age Filter] &&
                                    [Name Filter] &&
                                    [Sex Filter])
            ),
        /* Add additional conditions for months 7 through 12 */
        0  -- Default case if no conditions are met
    )
    , 0  -- Return 0 if not FY24
)

Explanation of the DAX Function

Variables:
- SelectedDate: Retrieves the most recent date from the date slicer.
- SelectedMonth: Extracts the month from SelectedDate.
- SelectedYear: Obtains the year from SelectedDate.
- IsFY24: A boolean check to confirm the year is FY 2024.
Main Logic:
- Use SWITCH to create conditions for each month.
- Different sums (e.g., SUM(Sales[M1], Sales[M2], Sales[M3]) are calculated based on the selected month.
- FILTER is employed to include dynamic filters specified by the user (like smoker status, age, etc.).
Context Control: The FILTER(ALLSELECTED(Sales), ...) structure allows users to maintain their filters while summing values.

Usage Example

In a scenario where you wish to evaluate monthly sales performance, this formula will automatically adjust and provide the sum of sales based on the month selected from your date slicer, while accounting for any additional filters set on your visuals.

Considerations

Make sure your data model includes relevant tables like Sales, DateTable, and any parameter tables for filters.
If necessary, expand the SWITCH statement to accommodate all months of the fiscal year.

Conclusion

This DAX formula efficiently sums up relevant sales data based on selected dates and applies dynamic filtering, ensuring that users can derive insights effectively. For more advanced DAX training, consider exploring courses from the Enterprise DNA Platform to enhance proficiency in data modeling and DAX queries.