Overlapping Dates different reports

[Ferminmj]

I have two reports, a leave and official travel report. What I am trying to do is find any overlapping dates between the two reports? The reports cannot be merged together since they are from separate systems. I created and ID key that relates them both to a personnel roster. 

1. I need to find the dates that over lap

2. Once I find the dates, I need to figure out how long the person was actually gone. 

What I need to check is
The start or end of a leave period is in the middle of an offical travel period: 
1. ([leave.start]>=[offical.start] && [leave.start]<=[offical.end])  || ([leave.end]>=[offical.start] && [leave.end]<=[offical.end]) 
2. If true, find out how many days that they overlap and subtract that differnce from the overall total. 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Ferminmj]

Like the above. Trying to determine where dates over lap between the offical and Personnel Travel (Since you can take both in conjunction) like the below which thru into Power Apps

Then detemine the total days gone

[Bibiano_Geraldo]

Hi @Ferminmj ,

Let's assume you have two tables:

LeaveReport: with columns PersonID, LeaveStart, LeaveEnd
OfficialTravelReport: with columns PersonID, OfficialStart, OfficialEnd

1- Create a measure that checks if there is an overlap between the leave and official travel periods:

OverlapDays = 
VAR LeaveStart = SELECTEDVALUE(LeaveReport[LeaveStart])
VAR LeaveEnd = SELECTEDVALUE(LeaveReport[LeaveEnd])
VAR OfficialStart = SELECTEDVALUE(OfficialTravelReport[OfficialStart])
VAR OfficialEnd = SELECTEDVALUE(OfficialTravelReport[OfficialEnd])

-- Check if the leave period overlaps with the official travel period
VAR OverlapStart = MAX(LeaveStart, OfficialStart)  -- The later start date
VAR OverlapEnd = MIN(LeaveEnd, OfficialEnd)        -- The earlier end date

-- Calculate the number of overlap days, ensuring it is positive
VAR OverlapDuration = 
    IF(OverlapStart <= OverlapEnd, 
        DATEDIFF(OverlapStart, OverlapEnd, DAY), 
        0
    )

RETURN
    OverlapDuration

2- Create another measure that calculates the total number of days a person was "actually gone," subtracting the overlap from the total leave period:

ActualLeaveDays = 
VAR TotalLeaveDuration = DATEDIFF(LeaveReport[LeaveStart], LeaveReport[LeaveEnd], DAY)
VAR OverlapDuration = [OverlapDays]  -- Using the previous measure for overlap

RETURN 
    TotalLeaveDuration - OverlapDuration

[Ferminmj]

First Measure
OverLapDays =

VAR LeaveStart= SELECTEDVALUE(MOL[Pers Tempo Track Start Date])

VAR LeaveEnd = SELECTEDVALUE(MOL[Pers Tempo Track Act Rtrn Dt])

VAR TADStart = SELECTEDVALUE(DTS[Departure Date])

VAR TADEnd = SELECTEDVALUE(DTS[Return Date])

---Checking for Overlap----

VAR OverlapStart = MAX(LeaveStart,TADStart) ---Find Latest Start

VAR OverlapEnd = MIN(LeaveEnd,TADEnd) --Find Earliest End

--Calculate the Overdays

VAR OverlapDuration =

    If(OverlapStart<= OverlapEnd,

        DATEDIFF(OverlapStart,OverlapEnd,DAY),0)

        RETURN

        OverlapDuration

Second Measure doid not like the Datediff without the selected Values

ActualLeaveDays =

VAR TotalLeaveDuration = DATEDIFF( SELECTEDVALUE(MOL[Pers Tempo Track Start Date]), SELECTEDVALUE(MOL[Pers Tempo Track Act Rtrn Dt]),DAY)

VAR OverLapDuration = [OverLapDays] --Using The previousMeasure

RETURN

TotalLeaveDuration - TotalLeaveDuration

I don't understand what to do with the measures when created. I broke down the variables into seperate measures and still got nothing, also to nut sure where to put the measure.

[Ferminmj]

Used your measure and got zero on the results, used the selected column to get the date to work

[Ferminmj]

Thanks, I am getting an error whern trying to addthe Date difference to the measure. I can't event create it as its own measure. I did create a column that does the same thing can I use that? Also how do you recommend getting the data back? What table would you put it in to see?