Custom Date Range Selection slicer help

STEVE_WT ,

To simplify the formula, you can combine all the date ranges into a single UNION and then use ADDCOLUMNS to dynamically assign the "Type" value to each date based on the range it falls into. This eliminates the need to repeat ADDCOLUMNS multiple times. The SWITCH function is used within ADDCOLUMNS to determine the type for each date, making the formula more concise and easier to maintain.

Here’s the optimized DAX:

Date Periods = 
ADDCOLUMNS(
    UNION(
        DATESMTD('Calendar'[Date]),
        DATESYTD('Calendar'[Date]),
        DATESQTD('Calendar'[Date]),
        PREVIOUSYEAR(DATESYTD('Calendar'[Date])),
        PREVIOUSMONTH('Calendar'[Date]),
        FILTER('Calendar', 'Calendar'[Date] = TODAY() - 1),
        FILTER('Calendar', 'Calendar'[Date] >= TODAY() - 14 && 'Calendar'[Date] < TODAY())
    ),
    "Type",
    SWITCH(
        TRUE(),
        'Calendar'[Date] IN DATESMTD('Calendar'[Date]), "MTD",
        'Calendar'[Date] IN DATESYTD('Calendar'[Date]), "YTD",
        'Calendar'[Date] IN DATESQTD('Calendar'[Date]), "QTD",
        'Calendar'[Date] IN PREVIOUSYEAR(DATESYTD('Calendar'[Date])), "LAST YEAR",
        'Calendar'[Date] IN PREVIOUSMONTH('Calendar'[Date]), "PREVIOUS MONTH",
        'Calendar'[Date] = TODAY() - 1, "YESTERDAY",
        'Calendar'[Date] >= TODAY() - 14 && 'Calendar'[Date] < TODAY(), "LAST 14 DAYS",
        BLANK()
    )
)

This approach applies ADDCOLUMNS once to the combined result of all the date ranges and assigns the "Type" field dynamically using the SWITCH function. The formula is now more streamlined and avoids redundancy, while maintaining the same functionality.

Best regards,

Best regards,