Forum Discussion
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 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 |
16 Replies
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/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?
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".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 ChenIf 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] & "--" & x2. adding a slicer:
Doing like this also enables dynamic changes.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
