Forum Discussion

STEVE_WT's avatar
STEVE_WT
Frequent Visitor
1 year ago
Solved

Custom Date Range Selection slicer help

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
Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

3 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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"
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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