topic Re: Filter table based on daily dates that fall within selected start and end date from another table in DAX Commands and Tips

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

Anonymous — Fri, 26 Jan 2024 10:40:14 GMT

Hi Power BI Community,

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.

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:

Created measures in JobTable for selected start and end dates using SELECTEDVALUE.

SelectedEndDate = SELECTEDVALUE(Sheet1[End Date])

SelectedStartDate = SELECTEDVALUE(Sheet1[Start Date])

2. Created a column in DateTable to check if the date is within the selected date range.

IsDateInRange =

IF(

'Sheet1'[Date]>= [SelectedStartDate] &&

Sheet1[Date]<= [SelectedEndDate],

"Yes",

"No"

)

3. Use the new column as a filter for "Yes" in the DateTable

However the categorisation and filtering with this IsDateInRange column is not working as expected. Dates outside of the selected range are still showing up as "Yes".

See screenshot below and my problem replicated here

Can anyone guide me on the correct approach or suggest improvements to my DAX calcs?

Thank you in advance for your assistance!

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

amustafa — Tue, 23 Jan 2024 11:58:37 GMT

Update your IsDateInRange as following.

IsDateInRange =

IF(

MIN(Sheet1[Date]) >= [SelectedStartDate] && MAX(Sheet1[Date]) <= [SelectedEndDate],

"Yes",

"No"

)

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

Anonymous — Tue, 23 Jan 2024 13:28:29 GMT

Hi amustafa,

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".

I've shared a screenshot example below

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

amustafa — Tue, 23 Jan 2024 14:50:35 GMT

My bad...here's the updated code: Also remove the column Date form the bottom visual

IsDateInRange =

VAR CurrentDate = MAX(Sheet1[Date])

VAR StartDate = [SelectedStartDate]

VAR EndDate = [SelectedEndDate]

RETURN

IF(

ISBLANK(StartDate) || ISBLANK(EndDate),

"No", // Return "No" if either start or end date is not selected

IF(

CurrentDate >= StartDate && CurrentDate <= EndDate,

"No",

"Yes"

)

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

Anonymous — Tue, 23 Jan 2024 17:38:15 GMT

Hi amustafa

No worries, I tried your 2nd suggestion but unfortunately that doesn't seem to work either. Even when I remove the Date column.

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.