Date Picker in slicer

[rnola16]

Hello all,

I have an dynamic M query with a date parameter on Birth Date as 'Param_StartDate'. Created a blank query using power query listing all dates from 2004-2010 with 'Date_Col' as column. Later in the data model on the 'Date_Col' did a Bind to Parameter on 'Param_StarDate'.

Now on the report, using 'Date_Col' as the field in the slicer I do not get to do a date picker. I followed what Patrick  mentioned in his blog but the option to choose 'between' doesnt work. I can only do the dropdown, vertical list, Tile only. Any ideas ?

Appreciate any help.

[rnola16 · Accepted solution]

What is I observe is, if the column is 'Bind to Parameter' you only see Vertical List, Tile and Dropdown.

But when it is NOT binded to Parameter you see Between, before, After Relative date and Time.

The Parameter 'Param_StartDate' is defiend as 'Date' data type.

the column EMPL.BIRTH_DT is a 'Date' data type in the database.

And the where clause in the M code reads as 

"where EMPL.BIRTH_DT = ' "&Date.ToText(Date.From(Param_StartDate),"yyyy-MM-dd")&" '

[V-yubandi-msft]

Hi @rnola16 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

[V-yubandi-msft]

Hi @rnola16 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[HarishKM]

@rnola16 Hey,

Kindly follow below steps

Step 1: Set 'Date_Col' to Date type- In Power Query, ensure the column is explicitly set to Date (right-click column → Change Type → Date).

Step 2: Use a separate unbound date table for slicer- Don’t use the parameter-bound version of the date table in the slicer. Create a standalone date table (e.g., SlicerDateTable) for the slicer.

Step 3: Mark as Date Table- Go to Model View, select the SlicerDateTable, then on the ribbon choose "Mark as Date Table", and pick the 'Date_Col' column.
Note : This enables full date slicer functionality, including the "Between" option.

Step 4: Use DAX to capture slicer values:
Create DAX measures to read the selected date range from the slicer:
something like this
SelectedStartDate = MIN('SlicerDateTable'[Date_Col])
SelectedEndDate = MAX('SlicerDateTable'[Date_Col])

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[V-yubandi-msft]

Hi @rnola16 ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

[V-yubandi-msft · Accepted solution]

Hi @rnola16 ,

Thank you for sharing the details and your valuable observations about date slicers in Power BI with dynamic M query parameters.

You're  right, When a field like Date_Col is linked to a parameter such as Param_StartDate, the slicer options become limited. You can only use basic types like Dropdown, Tile, or Vertical List, while advanced options like Between, Before, After, or Relative Date/Time are no longer available.

This behavior is intentional and is a known limitation of dynamic M query parameters in Power BI, as mentioned in the official Microsoft documentation.

Link:Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Also, thank you @kushanNa , for referencing the helpful video and prior blog post which demonstrated this limitation and offered practical guidance.

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

[rnola16 · Accepted solution]

What is I observe is, if the column is 'Bind to Parameter' you only see Vertical List, Tile and Dropdown.

But when it is NOT binded to Parameter you see Between, before, After Relative date and Time.

The Parameter 'Param_StartDate' is defiend as 'Date' data type.

the column EMPL.BIRTH_DT is a 'Date' data type in the database.

And the where clause in the M code reads as 

"where EMPL.BIRTH_DT = ' "&Date.ToText(Date.From(Param_StartDate),"yyyy-MM-dd")&" '

[kushanNa]

Correct it stopped working as a usual between, before, After Relative date and Time as soon as you bind it . Not sure why it happens like that , I have tried it last year , if you are still having this issue then it's a limitation & they have not change it yet 🙂 

[kushanNa]

If you're trying to do the same thing this developer is doing in here https://www.youtube.com/watch?v=pXU_SpiBWUA&t=1291s&ab_channel=DataPlatformCentral

then When you bind the column to a parameter, the date slicer will change into a dropdown and will no longer give you the option to use it as a regular calendar-style date picker. note that this video is 3 years old  

[rnola16]

Yes, been through this from Data Platform. I believe its a flaw in the application to not let Between, Relative Date/time, Before , After when the field itself is set to 'Date'. Microsoft should provide its own visual like 'Date Picker' in its visual List.

[rnola16]

Alternatively, created a dynamic date prompts where user can enter dates in the field along with other parameters and run it live. Avoids the hassle of scrolling down for the dates.

[V-yubandi-msft]

Hi @rnola16 ,

That’s an good  alternative using a dynamic date input prompt is an effective way to address the slicer limitations caused by parameter binding.

It is certainly more user-friendly for managing large date ranges and provides report consumers with better control over their input.

Thank you for sharing your work around.  If your issue is resolved, please consider marking it as the Accepted solution, as it could greatly assist others facing the same challenge.

Regards,

Yugandhar.

[Deku]

Is the field set a date type?

[rnola16]

Yes.