Attempting to determine overlap between date slicer and contract lifespan

[kvanlavieren]

Hi all,

I have a data set that includes customer contracts. The contracts have a start and end date. I want to be able to set a slicer with a start and end date. From that sliced date range, I want to determine how many days between the start and end date of the contract fall within the date range. 

At this point, I have created a date table called DateTable. You can set the date range through a slicer. The problem I encounter is the following: I want to determine the latest date between every row's (and thus contract) start date and the earliest date of the slicer. Similarly, I want to determine the earliest date between the end of the contract and the end of the slicer.

Through a calculated column, I cannot process the date range (as the column is calculated prior to setting the filter). Yet, through a measure I am not able to determine the dates PER row (as every contract has different start and end dates).

Any clues on how to solve this?

[Anonymous · Accepted solution]

Hi @kvanlavieren ,

Based on your description, I created these data.

You can create these measures.

LatestStartDate = 
VAR SlicerStartDate = MIN('DateTable'[Date])
RETURN
CALCULATE(
    MAX('Contracts'[StartDate]),
    'Contracts'[StartDate] >= SlicerStartDate
)


EarliestEndDate = 
VAR SlicerEndDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
    MIN('Contracts'[EndDate]),
    'Contracts'[EndDate] <= SlicerEndDate
)


DaysWithinRange = 
VAR LatestStart = [LatestStartDate]
VAR EarliestEnd = [EarliestEndDate]
RETURN
IF(
    ISBLANK(LatestStart) || ISBLANK(EarliestEnd),
    0,
    DATEDIFF(LatestStart, EarliestEnd, DAY)
)

The final result is shown below.

If you don't want to display data outside of the filtered dates, you can filter for non-blank data in the right filter pane.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[lbendlin]

INTERSECT() is your friend. And COUNTROWS() around it, too.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...