cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Selvarajen
Frequent Visitor

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 CompanyEntity NameValid FromValid To
LEGALLEGAL A12.31.199012.31.2000
LEGALLEGAL B01.01.200112.31.2010
LEGALLEGAL C01.01.201112.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 

1 ACCEPTED SOLUTION

Hi Selvarajen

 

Click here to try this demo 

 

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/zhWtU0DynCk 

 

Then 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 !

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@Selvarajen , refer if this blog on a similar topic can help. seem like you are looking for current employee kind of measure

 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

Hi Selvarajen

 

Click here to try this demo 

 

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/zhWtU0DynCk 

 

Then 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 !

View solution in original post

@speedramps Thank you so much. Of course, if there is an option to give you an heart, i would definitely do that.
Your solution helped me fix the problem and also use it in multiple other requirements.
I appreciate you taking the time to help me out here.

Best Regards,

Selvarajen

Awe ... thank you Selvarajen, that is the nicest thing anyone has said to me on forum!

Glad my solution helped. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.