Help with Filtering Multiple Date Columns Using Start and End Date

[tomperro]

First time poster.

I have a table that has multiple start and end dates that I need to filter based on a date slicer.

If I select 01/2022 as my slicer, I need to filter each start and end date as follows...

if ( End Date>=01/01/2022 Or (End Date Is Null AND Start Date]<=01/01/2022)

Expected Results

Department	Division	Implementation Zone	Category ID	Target
Transportation	New York	NYP	1	15
Electrical	Philadelphia	PHL	2	12

MonthlyTargetTable

Department	Department Start Date	Department End Date	Division	Division Start Date	Division End Date	Implementation Zone	Implementation Zone Start Date	Implementation Zone End Date	Category ID	Target
Transportation	01/01/2022		New York	01/01/2022		NYP	01/01/2022		1	10
Transportation	01/01/2022		New York	01/01/2022		NYP	01/01/2022		1	5
Electrical	01/01/2022	03/01/2022	Philadelphia	01/01/2022		PHL	01/01/2022		2	12
Mechanical	02/01/2022		Philadelphia	01/01/2022		PHL	01/01/2022		3	5

[v-yanjiang-msft · Accepted solution]

Hi @tomperro ,

According to your description, here's my solution.

Not sure if you have a date table as slicer, if not, create one.

Then create a measure.

Check =
VAR _S =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF (
        IF (
            MAX ( 'MonthlyTarget'[Department End Date] ) <> BLANK (),
            MAX ( 'MonthlyTarget'[Department End Date] ) >= _S,
            MAX ( 'MonthlyTarget'[Department Start Date] ) <= _S
        )
            && IF (
                MAX ( 'MonthlyTarget'[Division End Date] ) <> BLANK (),
                MAX ( 'MonthlyTarget'[Division End Date] ) >= _S,
                MAX ( 'MonthlyTarget'[Division Start Date] ) <= _S
            )
            && IF (
                MAX ( 'MonthlyTarget'[Implementation Zone End Date] ) <> BLANK (),
                MAX ( 'MonthlyTarget'[Implementation Zone End Date] ) >= _S,
                MAX ( 'MonthlyTarget'[Implementation Zone Start Date] ) <= _S
            ),
        1,
        0
    )

Put the measure in the visual and let it's value to 1. Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@tomperro , refer if one these two blogs can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Or the attached file can help