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 =
    SELECTEDVALUE (Period[Period])
VAR DynamicPeriod =
    SWITCH (
        TRUE(),
        SelectedPeriod = "Yesterday", today()-1,
        SelectedPeriod = "MTD", DATESMTD('Date'[CalendarDate]),
        SelectedPeriod = "QTD", DATESQTD('Date'[CalendarDate]),
        SelectedPeriod = "YTD", DATESYTD('Date'[CalendarDate]),
        SelectedPeriod = "Last Year", PREVIOUSYEAR('Date'[CalendarDate]),
        BLANK()
    )
RETURN
    DynamicPeriod
 

 

  • 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
            )
        )
)

19 Replies

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

    Hi ironboy0418 
    Place the measure in the filter pane of the table visual and select "is not blank" then apply the filter

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

      tamerj1 I did. However, it returns this error:

       

      The measure I've edited based on your answer above:

      RelativePeriod =
      VAR SelectedPeriod =
          SELECTEDVALUE (Period[Period])
      VAR DynamicPeriod =
          SWITCH (
      SELECTEDVALUE ( Period[Period] ),
          SelectedPeriod = "Yesterday",
              COUNTROWS ( FILTER ( 'Date', 'Date'[CalendarDate] = TODAY () - 1 ) ),
          SelectedPeriod = "MTD",
              COUNTROWS (
                  FILTER ( 'Date', 'Date'[CalendarDate] IN DATESMTD ( 'Date'[CalendarDate] ) )
              ),
          SelectedPeriod = "QTD",
              COUNTROWS (
                  FILTER ( 'Date', 'Date'[CalendarDate] IN DATESQTD ( 'Date'[CalendarDate] ) )
              ),
          SelectedPeriod = "YTD",
              COUNTROWS (
                  FILTER ( 'Date', 'Date'[CalendarDate] IN DATESYTD ( 'Date'[CalendarDate] ) )
              ),
          SelectedPeriod = "Last Year",
              COUNTROWS (
                  FILTER ( 'Date', 'Date'[CalendarDate] IN PREVIOUSYEAR ( 'Date'[CalendarDate] ) )
              )
          )
      RETURN
          DynamicPeriod
       

       

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

        ironboy0418 

        My mistake.

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