Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ironboy0418
Helper II
Helper II

How to create a custom relative date slicer using DAX?

‎03-13-2023 10:50 PM

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
 
ironboy0418_0-1678773005587.png

 

Message 1 of 20
1,762 Views
0
3 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 02:40 AM

@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] ) )
        )
)

 

View solution in original post

Message 10 of 20
1,664 Views
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 02:51 AM

@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 ()
                    )
            )
        )
)

View solution in original post

Message 12 of 20
1,648 Views
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 11:29 PM

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

View solution in original post

Message 19 of 20
1,532 Views
19 REPLIES 19
tamerj1
Super User
Super User

‎03-13-2023 11:37 PM

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] ) )
        )
)
Message 2 of 20
1,720 Views
0
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 12:54 AM

@tamerj1 I did. However, it returns this error:

ironboy0418_0-1678780377111.png

 

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
 
ironboy0418_1-1678780458166.png

 

 
Message 3 of 20
1,707 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 12:56 AM

@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] ) )
        )
)
Message 4 of 20
1,704 Views
0
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 01:28 AM

@tamerj1 I did copy the formula above however, it doesn't return the desired result. As I selected MTD, it should filter the March MTD dates only:

ironboy0418_0-1678782473529.png

 

DAX Formula:

ironboy0418_2-1678782618575.png

 

 

 

Message 5 of 20
1,697 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 01:40 AM

Hi @ironboy0418 
Can you please in little more details what should be the expected results?

Message 6 of 20
1,691 Views
0
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 02:10 AM

 My apologies. When I select MTD in the slicer it should return the dates only for March 1, 2023 to date. However, it returns all of the dates from January 2022. Same goes with YTD 

 

Not sure but it works when I select 'yesterday' in the slicer:

 

Maybe, the dax formula that uses IN?

 

 

COUNTROWS (
            FILTER ( 'Date', 'Date'[CalendarDate] in DATESMTD ( 'Date'[CalendarDate] ) )
        )

 

ironboy0418_1-1678784999304.png

ironboy0418_0-1678784969018.png

@tamerj1

Message 7 of 20
1,687 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 02:25 AM

@ironboy0418 
So YTD and MTD should return the date starting from the year of today and the month of today respectively. Right? What about other slicer options? Any issues?

Message 8 of 20
1,678 Views
0
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 02:28 AM

Yes correct. Same issue with 'Last Year' data. I initially write dax formula for Yesterday, MTD, QTD, YTD, and Last Year. Will update when we figure out the solution 🙂

Message 9 of 20
1,676 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 02:40 AM

@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] ) )
        )
)

 

Message 10 of 20
1,665 Views
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 02:45 AM

@tamerj1 It worked for YTD, QTD, MTD! Thank you so much. Will try to do for the previous year

Message 11 of 20
1,654 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 02:51 AM

@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 ()
                    )
            )
        )
)
Message 12 of 20
1,649 Views
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 05:51 AM

@tamerj1Can I also ask how to write dax to filter last week dates? I created a variable but not sure how to execute it inside your dax formula:

 

VAR PREVIOUSWEEK = FILTER(ALL('Date'),'Date'[Week Rank]=MAX('Date'[Week Rank])-1)
Message 14 of 20
1,606 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 06:12 AM

@ironboy0418 

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'[CalendarDate]
                    = CALCULATE (
                        MAX ( 'Date'[Week Rank] ),
                        'Date'[CalendarDate] = TODAY (),
                        ALL ( 'Date' )
                    ) - 1
            )
        )
)
Message 15 of 20
1,599 Views
0
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 03:39 PM

@tamerj1 Thanks for this! However, it returns no result when I select 'Last Week' I'm not sure why. Here's my dax formula for Week Rank:

 

Week Rank = RANKX(ALL('Date'),'Date'[Start of Week],,ASC,Dense)
 
ironboy0418_0-1678833550991.pngironboy0418_1-1678833566779.png

 

Message 16 of 20
1,575 Views
0
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 08:15 PM

@ironboy0418 

what is the last date in your date table

Message 17 of 20
1,552 Views
0
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 10:29 PM

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

ironboy0418_0-1678858165738.png

 

 

 

Message 18 of 20
1,543 Views
tamerj1
Super User
Super User
In response to ironboy0418

‎03-14-2023 11:29 PM

@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
            )
        )
)
Message 19 of 20
1,533 Views
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-15-2023 12:02 AM

Thank you so much for this @tamerj1 It worked:

ironboy0418_0-1678863731770.png

 

Message 20 of 20
1,522 Views
ironboy0418
Helper II
Helper II
In response to tamerj1

‎03-14-2023 02:52 AM

Great! Thank you so much for the help! I really appreciate it!

Message 13 of 20
1,640 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All