Custom Date Range Selection slicer help

[STEVE_WT]

Hi

I have a slicer with custom date ranges but i am struggling to add in 'yesterday' and 'last 14 days'. See my code below :

Date Periods = UNION(ADDCOLUMNS(DATESMTD('Calendar'[Date]), "Type", "MTD"), ADDCOLUMNS(DATESYTD('Calendar'[Date]), "Type", "YTD"), ADDCOLUMNS(DATESQTD('Calendar'[Date]), "Type", "QTD"), ADDCOLUMNS(PREVIOUSYEAR(DATESYTD('Calendar'[Date])), "Type", "LAST YEAR"), ADDCOLUMNS(PREVIOUSMONTH('Calendar'[Date]), "Type", "PREVIOUS MONTH"))

Any help would be greatly appeciated

[Anonymous]

Hi, @STEVE_WT 

Thanks for the reply from johnt75 and DataNinja777. You can try the dax they give and if it helps, you can mark it as a solution. If it still doesn't work, you can share the pbix file without sensitive data for testing.

Best Regards,
Yang
Community Support Team

[johnt75]

You can use

Date Periods =
VAR CurrentDate =
    TREATAS ( { TODAY () }, 'Calendar'[Date] )
RETURN
    UNION (
        ADDCOLUMNS (
            CALCULATETABLE ( DATESMTD ( 'Calendar'[Date] ), CurrentDate ),
            "Type", "MTD"
        ),
        ADDCOLUMNS (
            CACULATETABLE ( DATESYTD ( 'Calendar'[Date] ), CurrentDate ),
            "Type", "YTD"
        ),
        ADDCOLUMNS (
            CALCULATETABLE ( DATESQTD ( 'Calendar'[Date] ), CurrentDate ),
            "Type", "QTD"
        ),
        ADDCOLUMNS (
            PREVIOUSYEAR ( CALCULATETABLE ( DATESYTD ( 'Calendar'[Date] ), CurrentDate ) ),
            "Type", "LAST YEAR"
        ),
        ADDCOLUMNS (
            CALCULATETABLE ( PREVIOUSMONTH ( 'Calendar'[Date] ), CurrentDate ),
            "Type", "PREVIOUS MONTH"
        ),
        ADDCOLUMNS (
            DATESINPERIOD ( 'Calendar'[Date], TODAY (), -1, DAY ),
            "Type", "Yesterday"
        ),
        ADDCOLUMNS (
            DATESINPERIOD ( 'Calendar'[Date], TODAY (), -14, DAY ),
            "Type", "Last 14 Days"
        )
    )

[DataNinja777 · Accepted solution]

@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,