Forum Discussion

Ferminmj's avatar
Ferminmj
Regular Visitor
1 year ago

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...
Need Help
Tips and Tricks
Tutorial Requests
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
    Ferminmj
    Regular Visitor
    1 year ago

    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?

     

  • Ferminmj's avatar
    Ferminmj
    Regular Visitor
    1 year ago

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

  • Ferminmj's avatar
    Ferminmj
    Regular Visitor
    1 year ago
    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.