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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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,210 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,112 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,096 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
980 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,168 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,155 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,152 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,145 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,139 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,135 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,126 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,124 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,113 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,102 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,097 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,054 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,047 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,023 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,000 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
991 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
981 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
970 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,088 Views

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All