custom date slicer with start and end date values

[Meera556]

Hi,

I'm trying to create a custom date slicer to a report.By default the date slicer should be filtered to the last 30 days from the current date.I have added a condition to the filters on the visual to limit to the last 30 days.

But I need to select between the start date and end date. If I apply the above filter condition its allowing only to select between that 30 days time period only.

I have done this using parameters in tableau.I'm new to power bi and can anyone help doing with this using parameters in power bi.

Thanks for your help in advance.

[Anonymous · Accepted solution]

Hi, @Meera556 

Based on your information, I create a sample table:

First, create a calendar table that includes all the dates you need:

Calendar = CALENDAR(MIN('Table'[Date]), MAX('Table'[Date]))

Then create a measure, here is the DAX expression:

Measure = 
VAR cc=MAX('Calendar'[Date])
RETURN CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]>=cc-30&&'Table'[Date]<=cc))

Create a slicer, put the calendar date in this slicer. When you select a date, the table displays data for the last 30 days.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

Hi @Meera556 , You can achieve this using measures

Make sure you have one date table and if not create using 

DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)

Then Create a measure for last 30 days

IsLast30Days =
VAR Today = TODAY()
VAR Last30Days = Today - 30
RETURN
IF (
'DateTable'[Date] >= Last30Days && 'DateTable'[Date] <= Today,
1,
0
)

Then create a measure for selected date range 

SelectedStartDate =
MIN('DateTable'[Date])

SelectedEndDate =
MAX('DateTable'[Date])

Last measure to Filter data based on selected range

IsInSelectedDateRange =
VAR StartDate = [SelectedStartDate]
VAR EndDate = [SelectedEndDate]
RETURN
IF (
'YourDataTable'[Date] >= StartDate && 'YourDataTable'[Date] <= EndDate,
1,
0
)

Add a slicer for the date range selection:

Drag the Date column from your DateTable to the slicer visual.
Set the slicer to "Between" mode to allow users to select a start and end date.
Add a slicer for the last 30 days:

Drag the IsLast30Days measure to a slicer visual.
Set the slicer to "Single Select" mode and select "1" to filter for the last 30 days.
 Apply the Filter to Your Visuals
Add your data visual (e.g., table, chart) to the report.
Apply a visual-level filter using the IsInSelectedDateRange measure:
Drag the IsInSelectedDateRange measure to the visual-level filters pane.
Set the filter to "1" to only show data within the selected date range.

[Meera556]

Hi @bhanu_gautam ,

Thanks for your reply.

While trying to create measure for IsInSelectedDateRange it is not taking my date table date column its only accepting measures there and I'm not able to create IsLast30Days measure getting error near 'DateTable'[Date].

Can you please help me with this

[bhanu_gautam]

Just share some sample data with same column and table name I will try it and share PBIX file with you