topic Re: How to create a custom relative date slicer using DAX? in DAX Commands and Tips

How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 10:00:18 GMT

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

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 06:37:11 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 07:54:26 GMT

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

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 07:56:56 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 08:30:26 GMT

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:

DAX Formula:

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 08:40:30 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 09:15:24 GMT

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

tamerj1

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 09:25:02 GMT

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?

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 09:28:16 GMT

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 🙂

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 09:41:02 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 09:51:35 GMT

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

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 09:51:28 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 09:52:56 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 12:57:54 GMT

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)

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Tue, 14 Mar 2023 13:12:58 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Tue, 14 Mar 2023 22:39:55 GMT

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)

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Wed, 15 Mar 2023 03:15:45 GMT

ironboy0418

what is the last date in your date table

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Wed, 15 Mar 2023 05:29:33 GMT

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

Re: How to create a custom relative date slicer using DAX?

tamerj1 — Wed, 15 Mar 2023 06:29:44 GMT

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

Re: How to create a custom relative date slicer using DAX?

ironboy0418 — Wed, 15 Mar 2023 07:02:22 GMT

Thank you so much for this tamerj1 It worked: