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

[Anonymous]

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:

1. 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!

[amustafa]

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"

            )

        )

[Anonymous]

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.

[amustafa]

Update your IsDateInRange as following.

IsDateInRange =

    IF(

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

        "Yes",

        "No"

    )

[Anonymous]

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