topic Attempting to determine overlap between date slicer and contract lifespan in DAX Commands and Tips

Attempting to determine overlap between date slicer and contract lifespan

kvanlavieren — Fri, 06 Sep 2024 15:07:23 GMT

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?

Re: Attempting to determine overlap between date slicer and contract lifespan

lbendlin — Fri, 06 Sep 2024 20:25:26 GMT

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-Forum/ba-p/963216

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/1447523

Re: Attempting to determine overlap between date slicer and contract lifespan

Anonymous — Fri, 13 Sep 2024 09:09:36 GMT

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.