How to create 1 slicer to do filtering based on the values of 2 date type columns? (Start/End Date)

[mic_user]

I'm new to Power BI and would really appreciate any input on this.

By taking the table below as an example.

I'm looking to create a slicer and let's call it "Project Year" and it should return all the related projects (e.g. by selecting 2022 in the slicer, we should get project A and B because project B started in 2021 and ended in 2024, hence 2022 and 2023 (the years in between) are considered as its project years too).

Would like to highlight that there are projects with only start date or end date being entered, and also projects without any date being entered yet. By selecting 2022 in the slicer, would also like to include those with only start date/end date being entered as 2022

Can refer to more examples below.

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[OwenAuger · Accepted solution]

Hi @mic_user 

You can handle this with a variation on the Events in Progress pattern. Take a look at the # Open Orders ALL measure for example.

I have attached a small PBIX illustrating how I would set it up.

1. Given how you need to handle blank Start Date or End Date values, I recommend adding columns to the Projects table with this pseudocode (I added these in Power Query):

• Start Date Effective = COALESCE ( Start Date, End Date )
• End Date Effective = COALESCE ( End Date, Start Date)

2. In my example I assume that the 'Date' table has no relationship with the Projects table. If there is a relationship, see comments in code below.

3. Create these measures:

# Projects = 
COUNTROWS ( Projects )

# Projects in Progress = 
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    IF (
        NOT ISFILTERED ( 'Date' ),
        [# Projects],
        CALCULATE (
            [# Projects],
            Projects[Start Date Effective] <= MaxDate,
            Projects[End Date Effective] >= MinDate
            -- If there is a relationship between Date -> Projects,
            -- then add an argument: REMOVEFILTERS ( 'Date' )
        )
    )
RETURN
    Result

3. Create a visual grouped by Project, with visual-level filter "# Projects in Progress is not blank":

4. Filters on 'Date' columns such as 'Date'[Year] then filter this visual as expected:

Does something like this work for you?

[mic_user]

Thanks @OwenAuger , this works for me!