Dynamic DAX Formula for Conditional Summing

This document outlines a DAX function to dynamically sum values based on the selected month from a slicer and apply additional filters. The logic is designed to work specifically for months in the fiscal year 2024.

Requirements

• Inputs:

  • Date Slicer (to select a month)
  • Filters (for Product Name, Channel Name, Smoker Status, Age, Name, Sex)

• Logic:

  • If Month 4 is selected, sum Column M1.
  • If Month 5 is selected, sum Column M1 (for month 5) and Column M2 (for month 4).
  • If Month 6 is selected, sum Column M1 (for month 6), Column M2 (for month 5), and Column M3 (for month 4).
  • Continue this pattern for the entire fiscal year.

DAX Code

// Function to calculate dynamic sums across specified columns based on selected month
DynamicSumFY24 = 
VAR SelectedMonth = MONTH(SELECTEDVALUE('DateTable'[DateColumn])) // Get the selected month number
VAR CurrentYear = YEAR(SELECTEDVALUE('DateTable'[DateColumn])) // Get the selected year
VAR BaseSum = TOTALYTD(SUM('SalesTable'[M1]), 'DateTable'[DateColumn], "28/02") // Adjust date for fiscal year
VAR AdditionalSum =
    SWITCH(
        TRUE(),
        SelectedMonth = 5, TOTALYTD(SUM('SalesTable'[M2]), 'DateTable'[DateColumn], "28/02"), // Only if Month 5
        SelectedMonth = 6, TOTALYTD(SUM('SalesTable'[M2]), 'DateTable'[DateColumn], "28/02") + TOTALYTD(SUM('SalesTable'[M3]), 'DateTable'[DateColumn], "28/02"), // If Month 6
        BLANK()
    )

// Combine base sum with additional sums based on the selected month
RETURN
IF(SelectedMonth >= 4 && SelectedMonth <= 12,
    BaseSum + COALESCE(AdditionalSum, 0), // Add additional sums where applicable
    BLANK() // Return blank outside the range
)

Code Documentation

Function Details

• Function Name: DynamicSumFY24
• Purpose: To dynamically compute sums based on the selected month and given conditions for fiscal year 2024.
• Parameters:
  • None directly; relies on slicers and filter context from users.
• Returns: A numeric value representing the computed sum, or blank if conditions are not met.
• Exceptions: Handles invalid dates gracefully by returning BLANK.

Explanation of Code Logic

1. Selected Variables:

   • SelectedMonth: Retrieves the month number from the date selected in a slicer.
   • CurrentYear: Gets the year to ensure the summing logic matches the fiscal year.

2. Base Calculation:

   • BaseSum: Uses TOTALYTD to sum values in M1 column based on the fiscal year’s end assumption (adjust the date as per your fiscal year).

3. Additional Sums:

   • The SWITCH function adds conditions for summing columns M2 and M3 based on the selected month.

4. Final Return:

   • Combines the base sum and any additional sums, outputting a blank if no valid month is selected.

Example Usage

Assuming you have a slicer for dates connected to a 'DateTable', when a user selects a date belonging to April, May, or June of 2024, the measure DynamicSumFY24 can be placed in the values area of a Power BI visual to display aggregated values as per the rules defined above.

Additional Considerations

• Make sure the column headers (M1, M2, M3) and the SalesTable are aligned with your actual data model.
• Adjust BaseSum logic according to your fiscal year start date if different from the assumed dates.
• This measure can be further enhanced for performance and scalability as needed.

For further mastery of DAX and more best practices, consider exploring courses available on the Enterprise DNA Platform.