Date slicer that filters 2 dates

[sebaskl]

Hello everyone,

I have a dataset with contract data, and each contract has a start date and an end date.

My main objective is to have a slicer where I can input a certain date period (e.g., 01/01/2021 to 12/31/2024), and every contract that is active at anytime during that period has to appear, whether it is active during the whole length of the period or not.

The data is as follows:

contract_number	start_contract	end_contract
1	01/01/2020	12/31/2026
2	01/01/2023	12/31/2024
3	01/01/2024	12/31/2024
4	01/01/2024	12/31/2026
5	01/01/2025	12/31/2027
6	01/01/2020	12/31/2021

So, for example, I would want a filter like so:

Which would display every contract that is active within that period, even if it started before 01/01/2021 and/or expires after 12/31/2024. In this case, only contract 5 wouldn't be displayed, because its period is never coincides with the slicer's period.

Would very much appreciate some help here! Thanks everyone

[123abc]

In Power BI, you can achieve this by creating a custom date slicer that filters contracts based on your specified date range. You'll need to create a measure that checks if a contract is active during the selected date range using DAX (Data Analysis Expressions). Here's how you can do it:

1. Create a new measure in your Power BI model. Go to the "Modeling" tab in Power BI and select "New Measure."

2. Use the following DAX formula to create the measure:

Active Contracts =
VAR StartDate = MIN('YourSlicerTable'[StartDate])
VAR EndDate = MAX('YourSlicerTable'[EndDate])
RETURN
COUNTROWS(
FILTER(
'YourDataTable',
'YourDataTable'[start_contract] <= EndDate &&
'YourDataTable'[end_contract] >= StartDate
)
)

Replace 'YourSlicerTable' with the table you use for your slicer, and 'YourDataTable' with the name of your contract data table.

1. Create a slicer that allows you to select a date range (e.g., 01/01/2021 to 12/31/2024) on your report page.

2. Create a card visual and place it on the report page. Then, add the "Active Contracts" measure to the card. This will display the number of contracts that are active during the selected date range.

3. Additionally, you can use the "Active Contracts" measure to filter the table with contract data to display the active contracts in a table or other visualizations.

Now, when you select a date range using the slicer, the "Active Contracts" measure will dynamically calculate the number of contracts that are active during that period and display the results.

Remember to adjust the table and column names in the DAX formula according to your actual dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[sebaskl]

First of all thanks for the quick reply. I don't have the slicer table you talk, how would I create it?