Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
lizardi89
Helper I
Helper I

Dynamic range date slicer

Hello, I need help with a dynamic date slicer please...

 

So, I have two tables, one with client contract info and another with historic info from multiple clients.

 

I need to have a dynamic date slicer that enables dates ONLY from the Signature Date to until Today (since we also store historic data for other calculations), so far the code I have is the following:

 

 

Cal Filter = 
VAR contract_sign =
    CALCULATE(min('Contracts Data Base'[Signature Date]),
        filter('Contracts Data Base','Contracts Data Base'[Signature Date])
     )
RETURN
CALENDAR(contract_sign,TODAY())

 

 

 

The problem is that once I filter the report it always shows the date range from the MIN in the Signature Date column instead of the selected client.

 

For Example if I choose client "AA002" the date range goes from 12/10/19 to today, since "AA003" was signed before...

 

Any solutions?

 

Table 1:

Historic info from my multiple clients

Client CodeDateTotal Ad Spend
AA0021/1/204,534
AA0031/1/203,456
AA0021/2/206,785

 

Table 2:

Contract info for each Client

Client CodeSignature Date
AA00212/25/19
AA00312/10/19
 
16 REPLIES 16
v-lionel-msft
Community Support
Community Support

Hi @lizardi89 ,

 

The reason the date slicer return this result is because of your data model.

xx14.PNG

In fact, there is a 1: 1 relationship between the Contracts table and the "Cal Filter" table, and a *:1 relationship between the "Historic Data" table and the "Cal Filter" table (the "1" table to filter the " *"  table), so you cannot use the [Sourcename] column to filter out the date range. 

 

Best regards,
Lionel Chen

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

Hi Lionel,
Thank you for your help, would there be any way to fix the model to achieve what I need to do?

Hi @lizardi89 ,

 

For your problem, we can't solve it by modifying the model. Maybe you can do like this:

 

1. Creating a [Range] column in Contracts table:

Range = 
VAR x = TODAY()
RETURN
 [Signature Date] & "--" & x

 

zz3.PNG 

2. adding a slicer:

zz4.PNG

Doing like this also enables dynamic changes.

 

Best regards,
Lionel Chen

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

 

 

Greg_Deckler
Community Champion
Community Champion

Huh, I thought I had the answer to this but turns out that there might be a bug here. I figured it was a relationship direction thing or an Edit interactions thing. But I have set these both correctly and it still does not work right. See my file attached.

 

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

Not sure I got it. You can create a common date dimension. And join with both tables

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

To give a little more light on the matter...

Whenever I choose Client AA002 in the visuals I need the date slicer range to be from 12/25/19 to today(), instead it shows 12/10/19 to today() since the latter is the minimum value from the column [Signature Date]...

I need the slicer to select the specific value from the contract

Thanks

Have you tried creating another slicer for Client and edit the interactions of that slicer to make sure it filters the date slicer?

Yes I have, I have multiple filters, but they don't afect the slicer "from" value

Sounds like you want to have the date slicer filter based on the selected client.  If you create a second slicer for Client and make sure it filters the date slicer, it could work.
Capture.PNG
I am course saying this without having any knowledge of your data model...you'll probably need a relationship between the date table and signature date.

Yes I already tried that and it didn't work...

Can you share your PBIX?

Please do the following in your PBIX:

  1. Change the formula for your Cal Filter table to CALENDARAUTO()
  2. Mark it as a date table
  3. Slide the date slicer down a bit away from the client slicer to give yourself some room.
  4. Click the Client slicer then go to Format > Edit Interactions.  Make sure that the slicer is set to filter (see my earlier pic)
  5. Add Signature Date to the slicer instead of Cal Filter[Date]

Capture2.PNGCapture.PNG

 

You should be set...

Thank you for the quick response, but with that solution I don't get the slicer to be from Signature Date to TODAY, it just shows the signature date ...
I need it to go from signature date to today depending on the chosen client, for example:

ClientFromTo ('Today()')
AA00009923-Jan-202022-Mar-2020
AA00009314-Feb-202022-Mar-2020
AA0001186-Feb-202022-Mar-2020
AA00012118-Mar-202022-Mar-2020

Then change the slicer to "After" instead of "Between".  And make sure your date table filters out anything beyond YTD in Power Query.

 

The problem with what you're attempting is that you can't use a measure value in a slicer.  And additionally, calculated columns and tables created by DAX only update when the file is open or data is refreshed - and not when slicers change.  So creating a table to be used as the input to the slicer is shot as well.  What you're asking for isn't possible.

It still doesn't work, once I select a client the slicer will only move towards the signature date, and once it´s moved it doesn't let you go back...

The problem with putting the signature date in the slicer instead of the date is that it only has those options, not the whole range of dates since the signature until today

Thank you very much for your help tho

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors