Dynamic DAX Formula for Summing Columns Based on Selected Month

This document outlines a DAX function designed to dynamically sum specific columns based on a selected month from slicers in Power BI. The approach will handle the summation of values in a yearly context, considering the fiscal year 2024.

Requirements

• Input Slicers: The user will provide slicers for:
  • Date
  • Product Name
  • Channel Name
• Conditional Logic: The function will check the selected month and sum:
  • Column M1 for the current month.
  • Column M2 for the previous month.
  • Column M3 for two months prior.

DAX Code

DynamicSum = 
VAR SelectedMonth = MONTH(SELECTEDVALUE(Date[Date]))
VAR CurrentYear = YEAR(SELECTEDVALUE(Date[Date]))

RETURN
SWITCH(
    TRUE(),
    SelectedMonth = 4, SUMX(FILTER(Data, MONTH(Data[Date]) = 4), Data[M1]),
    SelectedMonth = 5, SUMX(FILTER(Data, MONTH(Data[Date]) = 5), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 4), Data[M2]),
    SelectedMonth = 6, SUMX(FILTER(Data, MONTH(Data[Date]) = 6), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 5), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 4), Data[M3]),
    SelectedMonth = 7, SUMX(FILTER(Data, MONTH(Data[Date]) = 7), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 6), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 5), Data[M3]),
    SelectedMonth = 8, SUMX(FILTER(Data, MONTH(Data[Date]) = 8), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 7), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 6), Data[M3]),
    SelectedMonth = 9, SUMX(FILTER(Data, MONTH(Data[Date]) = 9), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 8), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 7), Data[M3]),
    SelectedMonth = 10, SUMX(FILTER(Data, MONTH(Data[Date]) = 10), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 9), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 8), Data[M3]),
    SelectedMonth = 11, SUMX(FILTER(Data, MONTH(Data[Date]) = 11), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 10), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 9), Data[M3]),
    SelectedMonth = 12, SUMX(FILTER(Data, MONTH(Data[Date]) = 12), Data[M1]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 11), Data[M2]) + SUMX(FILTER(Data, MONTH(Data[Date]) = 10), Data[M3]),
    BLANK()
)

Explanation of the Code

1. Variables:

   • SelectedMonth: Extracts the month from the selected date.
   • CurrentYear: Determines the year from the selected date.

2. SWITCH Statement:

   • This structure is used to identify which month has been selected and returns the respective sum:
     • For Month 4: Only sums column M1.
     • For Month 5: Sums M1 for month 5 and M2 for month 4, and so on.

3. SUMX and FILTER:

   • SUMX: Iterates over a table created by FILTER, summing the relevant column based on the specified conditions.

4. Return Value: If no month matches, it returns BLANK().

Usage Example

To use this DAX function in Power BI:

1. Create a measure in your data model using the provided DAX code.
2. Add slicers for Date, Product Name, and Channel Name on your report canvas.
3. The measure will dynamically update to reflect the proper sums based on the selected month from the slicer.

Conclusion

This DAX formula provides a flexible and dynamic solution for conditional summing based on slicer selections. Ensure you have the correct context set in Power BI to make the most of this function. For more advanced DAX and Power BI techniques, consider exploring the Enterprise DNA Platform for structured courses and resources.