Forum Discussion
Overlapping Dates different reports
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 separat...
Bibiano_Geraldo
Super User
Super UserHi 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
First Measure
OverLapDays =
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
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.
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.