Complex date trouble in powerbi

Question

I don't figure out how to manage my problem with a unique simple date slicer.

I have a table with contracts begin and end date joined with a dimensional table Dates. I would like to count contracts over the period using this slicer. I would like to create an unique slicer with a time range using my Date dimensional table.

This slicer should select in my table all contracts (eg):

Started before 04/01/2017 and finished after the 04/01/2017.
Started before 04/01/2017 but not finished after 08/01/2017.
Started during period but not finished in this period.
Started and finished in this period.

Any idea how i should do this ?

W.B. W.B. · Accepted Answer · 2021-02-12T16:32:36

For starters, in this particular case, I would remove relationship between Date and contracts tables. The way relationships work is they limit resultset (rows) in the filtered table (on the many side of the relationship) to contain rows matchin exactly those values that are currently selected in the filtering table (on the one side of the relationship).

This is clearly not what you want, because:

you want to filter based on more than one column
you want a more complex filtering logic

After you've removed the relationship, all you have to do is:

How many contracts =
COUNTROWS (
    FILTER (
        Contracts,
        NOT (
            Contracts[start_date] > MAX ( Dates[date] )
                || Contracts[end_date] < MIN ( Dates[date] )
        )
    )
)

This is a simpler way to select contracts, as it requires only two conditions. This is simply saying that you don't want to see contracts (NOT) that end before the selected date range or start after it, which covers all the cases you described.

Complex date trouble in powerbi

1 Answers