Forum Discussion
Date Input manually by user to help with formula
Hi All,
I am here again with a unique qeustion. I read through some of the old posts and it doesnt seem to solve my issue.
So i have a data set that has information on entities and whether the name of the entity has been changed or not.
For example
| Parent Company | Entity Name | Valid From | Valid To |
| LEGAL | LEGAL A | 12.31.1990 | 12.31.2000 |
| LEGAL | LEGAL B | 01.01.2001 | 12.31.2010 |
| LEGAL | LEGAL C | 01.01.2011 | 12.31.2020 |
I would like to create a filter where the user needs to manually enter the date (preferably a text input) and this needs to be the criteria for date filter.
Example - If the user enters 05.01.1998, i would like to show the the valid information during the time frame. The problem is we do not have data for every single day during the last 30 years and from a usability perspective the data slicers are not helping the cause.
Is there a way we could include a dynamic date input and use that information to filter the values in the page/table ?
Any guidance would be highly appreciated
Hi Selvarajen
Note that you type the date in the slicer or you can select it from the calendar menu.
It then indicates which rows are valid. You can then adapt this to just show the rows you want.How to do it:-
Use the List.Date function to create a list of dates from 1990 to today.
Add a date slicer using this trick
https://youtu.be/zhWtU0DynCkThen add this measure
Indicator =
--- get slicer date
VAR pickdate = MIN(Picklist[Date])
--- create a subset of the table where the pickdate is between the valid from/to range
VAR data_within_range =
FILTER(Facts, pickdate >= Facts[Valid From] && pickdate <= Facts[Valid To])
Return
--- Is the subset empty ?
INT(NOT(ISEMPTY(data_within_range)))
+ 0
Remember we are BI community voluntrees so please click the thumbs-up for me taking the effort to help you and then accept the solution if it works. Thank you !
5 Replies
- amitchandak
Super User
Selvarajen , refer if this blog on a similar topic can help. seem like you are looking for current employee kind of measure
Dear Amit.
Thanks for your message and the blog.
It is very insightful but unfortunately, our date range might be from 1980 to 2030 (50 years) which will be very hard to filter from.
That is one of the reasons i was looking for a more dynamic user input text filter that could be used.
Is there any other way the date filter could have a search option, that would also minimize the effort as a work around
speedrampsHi Selvarajen
Note that you type the date in the slicer or you can select it from the calendar menu.
It then indicates which rows are valid. You can then adapt this to just show the rows you want.How to do it:-
Use the List.Date function to create a list of dates from 1990 to today.
Add a date slicer using this trick
https://youtu.be/zhWtU0DynCkThen add this measure
Indicator =
--- get slicer date
VAR pickdate = MIN(Picklist[Date])
--- create a subset of the table where the pickdate is between the valid from/to range
VAR data_within_range =
FILTER(Facts, pickdate >= Facts[Valid From] && pickdate <= Facts[Valid To])
Return
--- Is the subset empty ?
INT(NOT(ISEMPTY(data_within_range)))
+ 0
Remember we are BI community voluntrees so please click the thumbs-up for me taking the effort to help you and then accept the solution if it works. Thank you !
