Filter table based on daily dates that fall within selected start and end date from another table

Question

Hi Power BI Community,&nbsp;I am working on a Power BI report where I have two table visuals, JobTable and DateTable, both sourced from the same dataset. Each row in JobTable represents a specific job with start and end dates, and DateTable shows daily details.&nbsp;I would like to achieve the following: when I click on a row in JobTable, I want to dynamically filter DateTable to show only the dates falling within the selected job's start and end dates.Here's what I've attempted so far:&nbsp;Created measures in JobTable for selected start and end dates using SELECTEDVALUE.SelectedEndDate = SELECTEDVALUE(Sheet1[End Date])SelectedStartDate = SELECTEDVALUE(Sheet1[Start Date])&nbsp;&nbsp; &nbsp; &nbsp;2. Created a column in DateTable to check if the date is within the selected date range.&nbsp;IsDateInRange =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Sheet1'[Date]&gt;= [SelectedStartDate] &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sheet1[Date]&lt;=&nbsp; [SelectedEndDate],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Yes",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"No"&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;3. Use the new column as a filter for "Yes" in the DateTable&nbsp;However the categorisation and filtering with this&nbsp;IsDateInRange column is not working as expected. Dates outside of the selected range are still showing up as "Yes".&nbsp;See screenshot below and my problem replicated here&nbsp;&nbsp;Can anyone guide me on the correct approach or suggest improvements to my DAX calcs?&nbsp;Thank you in advance for your assistance!&nbsp;&nbsp;&nbsp;

amustafa · Answer

Update your&nbsp;IsDateInRange as following.&nbsp;IsDateInRange =&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; MIN(Sheet1[Date]) &gt;= [SelectedStartDate] &amp;&amp; MAX(Sheet1[Date]) &lt;= [SelectedEndDate],&nbsp; &nbsp; &nbsp; &nbsp; "Yes",&nbsp; &nbsp; &nbsp; &nbsp; "No"&nbsp; &nbsp; )&nbsp;

anonymous · Answer

Hi&nbsp;amustafa,&nbsp;Thanks for the suggetion. I tried it with the new calculation there and I'm now getting "No" for all dates. Even after I select a start and end date, the dates that are within range are still showing "No".&nbsp;I've shared a screenshot example below&nbsp;

amustafa · Answer

My bad...here's the updated code: Also remove the column Date form the bottom visual&nbsp;IsDateInRange =&nbsp; &nbsp; VAR CurrentDate = MAX(Sheet1[Date])&nbsp; &nbsp; VAR StartDate = [SelectedStartDate]&nbsp; &nbsp; VAR EndDate = [SelectedEndDate]&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISBLANK(StartDate) || ISBLANK(EndDate),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "No", // Return "No" if either start or end date is not selected&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentDate &gt;= StartDate &amp;&amp; CurrentDate &lt;= EndDate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "No",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Yes"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;

anonymous · Answer

Hi&nbsp;amustafa&nbsp;&nbsp;No worries, I tried your 2nd suggestion but unfortunately that doesn't seem to work either. Even when I remove the Date column.&nbsp;But ideally I'd like to keep the date column this in the 2nd table to show that we're only keeping the dates that fall within any selected date range from the 1st table.

Forum Discussion

Filter table based on daily dates that fall within selected start and end date from another table

4 Replies

Related content

Filter depending on another filter

Slicer Filtering Another slicer

slicers automatically selected based on another slicer selection

updating one tile based on selection in another tile

Dynamic Filtering based on date selection