How to create 1 slicer to do filtering based on the values of 2 date columns?

[mic_user]

Hi, 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).

Can refer to more examples below.

Thank you.

[v-sgandrathi]

HI @mic_user,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.

[v-sgandrathi]

Hi @mic_user,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you.

[PaulDBrown]

Here is one way..
I've created an independent year table listing all the years contained in the start and end dates. I've created it using DAX, though it is probably easier to create it in Power Query

Year Slicer =
VAR _Start =
    VALUES ( 'DataTable'[Start Date] ) // Creates a table with all the Start Dates
VAR _End =
    VALUES ( 'DataTable'[End Date] ) // Creates a table with all the End Dates
VAR _DateTable =
    UNION ( _Start, _End ) // Appends both tables into a single table
VAR _MinDate =
    MINX ( _DateTable, [Start Date] ) // Returns the minimum date in the appended table
VAR _MaxDate =
    MAXX ( _DateTable, [Start Date] ) // Returns the maximum date in the appended table
VAR _MinYear =
    YEAR ( _MinDate ) // Returns the minimum Year
VAR _MaxYear =
    YEAR ( _MaxDate ) // Returns the maximum year
VAR _YearTable =
    GENERATESERIES ( _MinYear, _MaxYear, 1 ) // Creates a table listing all years from the minimum to the maximum year
RETURN
    _YearTable

This is the table to be used as the slicer and has no relationships with the table in the model containing the projects with start an end dates.

The model:

Create a measure to use as a filter for the table visual listing the projects and start and end dates.

Filter DataTable =
VAR _StartYear =
    YEAR ( MAX ( 'DataTable'[Start Date] ) ) // Returns the Year for the start date of each row
VAR _EndYear =
    YEAR ( MAX ( 'DataTable'[End Date] ) ) // Returns the Year for the end date of each row
VAR _YearValues =
    GENERATESERIES ( _StartYear, _EndYear, 1 ) // Creates a table listing all the years containd between and including the start date and end date for each row
VAR _Filter =
    COUNTROWS ( INTERSECT ( VALUES ( 'Year Slicer'[Value] ), _YearValues ) ) // Counts the rows of a virtual table which returns the years which match between those selected in the slicer and those contained in each row of the data table
RETURN
    IF ( ISFILTERED ( 'Year Slicer'[Value] ), _Filter, 1 )
// If the slicer is filtered, returns the number of coinciding years. If not, it returns a value of one to list all rows

Now create a table visual with the project, start and enda dates. Add the [Filter  DataTable] measure as a filter to the visual in the filter pane and set the value to greater or equal to 1:

 Add a slicer for the years. The slicer will return the rows which contain the year(s) selected in the slicer:

Here is the link to the sample PBIX:

Filter projects by selected years 

[v-sgandrathi]

Hi @mic_user,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

[bhanu_gautam]

@mic_user 

You can create a date table using DAX. Go to the "Modeling" tab and select "New Table", then enter the following DAX formula:

DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2000, 1, 1), DATE(2030, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date])
)

Create a relationship between the DateTable[Date] and your project table's Start Date and End Date. This can be done in the "Model" view by dragging and dropping the fields to create the relationships.

In your project table, create a calculated column to determine if a date from the date table falls within the project's start and end dates. Go to the "Modeling" tab and select "New Column", then enter the following DAX formula:

ProjectYear =
VAR SelectedYear = YEAR(DateTable[Date])
RETURN
IF (
SelectedYear >= YEAR(Project[Start Date]) && SelectedYear <= YEAR(Project[End Date]),
SelectedYear,
BLANK()
)

Add a slicer to your report.
Use the Year column from the DateTable as the field for the slicer.

To filter the projects based on the slicer selection, you can use the ProjectYear column in your visualizations. When you select a year in the slicer, it will filter the projects that have that year within their start and end dates.

[mic_user]

@bhanu_gautam Thanks for the input but when I'm trying to create a new column with the provided formula, an error is triggered for the highlighted part (Date) below.

ProjectYear =
VAR SelectedYear = YEAR(DateTable[Date])
RETURN
IF (
SelectedYear >= YEAR(Project[Start Date]) && SelectedYear <= YEAR(Project[End Date]),
SelectedYear,
BLANK()
)

Error: A single value for column 'Date' in table 'DateTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[bhanu_gautam]

@mic_user , Try using

DAX
ProjectYear =
VAR SelectedYear = YEAR(DateTable[Date])
RETURN
IF (
SelectedYear >= YEAR(EARLIER(Project[Start Date])) && SelectedYear <= YEAR(EARLIER(Project[End Date])),
SelectedYear,
BLANK()
)

[mic_user]

@bhanu_gautam 

With this, there will be additional errors for those fields in EARLIER().

The error is removed after I did something as below (addiing function like MAX after VAR)

ProjectYear =
VAR SelectedYear = YEAR(MAX(DateTable[Date]))
RETURN
IF (
SelectedYear >= YEAR(Project[Start Date]) && SelectedYear <= YEAR(Project[End Date]),
SelectedYear,
BLANK()
)

However, I realised with the provided method, we are creating 2 relationships between project table and date table (one for start date -> date, one for end date -> date) and Power BI only allows one active relationship between same tables (is this going to be an issue?). And I realised with the latest formula (by adding MAX() function), the slicer only applies to the start date (e.g. when 2024 is selected, only project with start date of 2024 will be returned, but not those projects started in 2023 and ended in 2025, and also not those project with end date in 2024).