Dynamic range date slicer

[lizardi89]

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 Code	Date	Total Ad Spend
AA002	1/1/20	4,534
AA003	1/1/20	3,456
AA002	1/2/20	6,785

Table 2:

Contract info for each Client

Client Code	Signature Date
AA002	12/25/19
AA003	12/10/19

[v-lionel-msft]

Hi @lizardi89 ,

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

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.

[lizardi89]

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

[v-lionel-msft]

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

 

2. adding a slicer:

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]

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".

[amitchandak]

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/

[lizardi89]

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

[littlemojopuppy]

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

[lizardi89]

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

[littlemojopuppy]

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.

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.

[lizardi89]

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

[littlemojopuppy]

Can you share your PBIX?

[lizardi89]

Sure, here's a quick demo

https://getfairplay-my.sharepoint.com/:u:/p/javier/Ef-dxUAh4CdNoh9nZ6vfDn0BpCuUfFMcD0ze57GnpSwc5g?e=...

[littlemojopuppy]

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]

You should be set...

[lizardi89]

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:

Client	From	To ('Today()')
AA000099	23-Jan-2020	22-Mar-2020
AA000093	14-Feb-2020	22-Mar-2020
AA000118	6-Feb-2020	22-Mar-2020
AA000121	18-Mar-2020	22-Mar-2020

[littlemojopuppy]

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.

[lizardi89]

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