Filter Table/Report based on User Selection of Slicer falls between Valid From Date & Valid To Date

[reyno1j]

Hey Everyone,

I have been trying to find an answer to my problem but to no avail, so hoping someone is able to provide some ideas / advice on if this is possible and if so, how to go about it?  I've tried various things and none have worked.

We create views within SQL and then connect to them using 'Import' from the Data Connectivity Mode when connecting to the SQL Server from within Power BI.  Within the view, we use tables that contain a 'Valid From Date' and 'Valid To Date' for each row of data, so when a change occurs a row is closed off and a new row is created.  This is so we can limit the rows of data within the table.

When trying to create a report within Power BI, we need to make it that the end user can use a data drop down list to select a date and the data within the whole of the report shows any rows that the selected date falls on or between the Valid From and Valid To dates.  We use Cards, Tables, Matrix and Charts within our reports, so all would need to reflect the date selected by the user.

I have tried various methods that I could think to get it to work but each have had limitations where it either doesn't work or only partly works.

Any help / advice on this would be really appreciated.

Many Thanks

Jon

[reyno1j]

Sorry for the late reply - I didn't get a notificaiton of the reply.

I managed to work out a soltuon using the post from @Anonymous as a guide.  Thank you so much for your hepl

[Anonymous · Accepted solution]

Hi @reyno1j ,

Please review the following links and check if they can help you achieve the requirement by using query parameter.

Dynamic M query parameters in Power BI Desktop

Power BI Quick Tip: Using Dynamic M Query Parameters with SQL​​

How to update M query parameters based on filters and slicers

Best Regards

[reyno1j]

@amitchandak  - Thanks for your reply.

I've had a quick look at both of these and they are useful, but I don't think they work for what we are trying to do.  I have only had a brief look but I need to go through them both more thoroughly to be sure they aren't suitable for this.

[amitchandak]

@reyno1j , Check if one of the two approaches can help

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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...