Forum Discussion

ironboy0418's avatar
ironboy0418
Icon for Helper II rankHelper II
3 years ago
Solved

How to create a custom relative date slicer using DAX?

Hello, I am trying to create a relative date slicer but I am not getting the desired results to filter the table. Here is my DAX formula:    RelativePeriod = VAR SelectedPeriod =     SELECTEDVA...
  • tamerj1's avatar
    tamerj1
    3 years ago

    ironboy0418 

    Please try

     

    RelativePeriod =
SWITCH (
    SELECTEDVALUE ( Period[Period] ),
    "Yesterday",
        COUNTROWS ( FILTER ( 'Date', 'Date'[CalendarDate] = TODAY () - 1 ) ),
    "MTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN FILTER (
                        DATESMTD ( 'Date'[CalendarDate] ),
                        FORMAT ( 'Date'[CalendarDate], "YYYYMM" ) = FORMAT ( TODAY (), "YYYYMM" )
                    )
            )
        ),
    "QTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN FILTER (
                        DATESQTD ( 'Date'[CalendarDate] ),
                        FORMAT ( 'Date'[CalendarDate], "YYYYQ" ) = FORMAT ( TODAY (), "YYYYQ" )
                    )
            )
        ),
    "YTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN FILTER (
                        DATESYTD ( 'Date'[CalendarDate] ),
                        YEAR ( 'Date'[CalendarDate] ) = YEAR ( TODAY () )
                    )
            )
        ),
    "Last Year",
        COUNTROWS (
            FILTER ( 'Date', 'Date'[CalendarDate] IN PREVIOUSYEAR ( 'Date'[CalendarDate] ) )
        )
)

     

  • tamerj1's avatar
    tamerj1
    3 years ago

    ironboy0418 
    I guess the following would solve all scenarios 

    RelativePeriod =
SWITCH (
    SELECTEDVALUE ( Period[Period] ),
    "Yesterday",
        COUNTROWS ( FILTER ( 'Date', 'Date'[CalendarDate] = TODAY () - 1 ) ),
    "MTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESMTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "QTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESQTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "YTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESYTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "Last Year",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        PREVIOUSYEAR ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        )
)
  • tamerj1's avatar
    tamerj1
    3 years ago

    ironboy0418 
    OMG! It is a very stupid mistake from my side. Please try below. I also added < before the = just in case the date of TODAY is not part of the date table.

    RelativePeriod =
SWITCH (
    SELECTEDVALUE ( Period[Period] ),
    "Yesterday",
        COUNTROWS ( FILTER ( 'Date', 'Date'[CalendarDate] = TODAY () - 1 ) ),
    "MTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESMTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "QTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESQTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "YTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESYTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "Last Year",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        PREVIOUSYEAR ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "Last Week",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[Week Rank]
                    = CALCULATE (
                        MAX ( 'Date'[Week Rank] ),
                        'Date'[CalendarDate] <= TODAY (),
                        ALL ( 'Date' )
                    ) - 1
            )
        )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

ironboy0418 

what is the last date in your date table

ironboy0418's avatar
ironboy0418
Icon for Helper II rankHelper II
3 years ago

tamerj1 the last date in my date table is the date today:

 

 

 

  • ironboy0418's avatar
    ironboy0418
    Icon for Helper II rankHelper II
    3 years ago

    Thank you so much for this tamerj1 It worked:

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ironboy0418 
    OMG! It is a very stupid mistake from my side. Please try below. I also added < before the = just in case the date of TODAY is not part of the date table.

    RelativePeriod =
SWITCH (
    SELECTEDVALUE ( Period[Period] ),
    "Yesterday",
        COUNTROWS ( FILTER ( 'Date', 'Date'[CalendarDate] = TODAY () - 1 ) ),
    "MTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESMTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "QTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESQTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "YTD",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        DATESYTD ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "Last Year",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[CalendarDate]
                    IN CALCULATETABLE (
                        PREVIOUSYEAR ( 'Date'[CalendarDate] ),
                        'Date'[CalendarDate] = TODAY ()
                    )
            )
        ),
    "Last Week",
        COUNTROWS (
            FILTER (
                'Date',
                'Date'[Week Rank]
                    = CALCULATE (
                        MAX ( 'Date'[Week Rank] ),
                        'Date'[CalendarDate] <= TODAY (),
                        ALL ( 'Date' )
                    ) - 1
            )
        )
)