Single date slicer

[Lavish]

Hi Friends,

I have a requirement where I have two date columns in one table ( Open date and Close date). I need to have a single date slicer, if I select the date the condition should apply to the table(visual) below. SELECTEDDATE <= (opendate) and SELECTEDDATE > (closedate).

I tried my haing two slicer one for opendate and one for closedate with (Before and After option), but my team is not accepting, they need only one date to be selected with that date the conditon should apply. 

Thanks,

Lavaniya

[Anonymous]

Hi @Lavish ,

First create a slicer table as below:

Calendar = CALENDAR(DATE(2022,1,1),DATE(2023,1,1))

Then create a measure as below.

Flag =

var _selectedvalue=

SELECTEDVALUE('Calendar'[Date])

return

IF(SELECTEDVALUE('Table'[Start Date])<=_selectedvalue&&SELECTEDVALUE('Table'[End Date])>=_selectedvalue,1,0)

Then add this measure to visual filter measure = 1.

Please refer to the following document for more information.

PowerBI how to use one slicer with two columns (dates) - Stack Overflow

Power BI From and To Date Filtering with One Slicer - RADACAD

Solved: Single slicer for start and end date - Microsoft Power BI Community

Best Regards,

Neeko Tang

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

[Lavish]

Date slicer is in diiferent table, the column need to be filter is in different table. When I choose the date slicer if not reflecting. Also the date visual should be something like this not as a list.

[Anonymous · Accepted solution]

Hi @Lavish ,

According to your description, I think the solution I provided above is based on a separate calendar table as a slicer to interact with your fact table, and you need to ensure that there is no inter-table relationship between the two tables, because the relationship between the two tables is manually defined by me writing measures, after implementing this premise, I think my scheme can perfectly achieve your needs, and for the calendar type date slicer you mentioned, I think it's just that the type of slicer is different. The essence is the same, you can achieve the same effect by placing the date column in the calendar table in your own slicer.

Best Regards,

Neeko Tang

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

[ppm1]

You can create a disconnected table with a single column of date values and use that in your slicer. You can then get the SELECTEDVALUE() or MIN() of that column and use it in your FILTER in your measure.

Here is one way to generate that table with DAX.

SlicerDates = CALENDAR(MIN(Table[OpenDate]), MAX(Table[CloseDate]))

Pat