Forum Discussion

Kerslaing's avatar
Kerslaing
Icon for Helper I rankHelper I
3 years ago

find conflicts across date ranges

We have data for tasks being completed within a date range and are going to show this on one of the gantt chart visuals.

I woudl also like to highlight any that are conflicting over the same day  but are struggling to get it to work, ive tried a few things from the forums but cant quite get them to display what i would like.

 

Data in the tables looks like

Ive created a conflicting column using 

 
Conflicting =
VAR StartDate = 'Changes (5 Year History)'[Scheduled_Start]
VAR EndDate = 'Changes (5 Year History)'[Scheduled_End]
VAR IDCurrent= 'Changes (5 Year History)'[Event_Ref]
RETURN
IF (
COUNTROWS(
FILTER (
ALL('Changes (5 Year History)'),
'Changes (5 Year History)'[Scheduled_Start] >= StartDate &&
'Changes (5 Year History)'[Scheduled_End] <= EndDate &&
'Changes (5 Year History)'[Event_Ref] <> IDCurrent
)
) > 0, TRUE(), FALSE())
 
 
But its not doing what it should

 

as you can see on the 14/04/2022 some are showing true but some are showing false.

 
 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Kerslaing ,

    Try a new column

    new column =

    var _cnt = countx(filter(Table, ([Start Date] >= earlier([Start Date] )  && [Start Date] <= earlier([End Date] )) || ([End Date] >= earlier([Start Date] )  && [EndDate] <= earlier([End Date] ) ), [Event_Ref])

    return

    if(isblank(_cnt), false(), True())

    • Kerslaing's avatar
      Kerslaing
      Icon for Helper I rankHelper I
      3 years ago

      Thanks,

       

      I tweaked it a bit so it excluded the current record it was on, 

      conf =
      VAR IDCurrent= 'Changes (5 Year History)'[Event_Ref]
      var _cnt = countx(
      filter('Changes (5 Year History)', ([Scheduled_Start] >= earlier([Scheduled_Start]) && [Scheduled_Start] <= earlier([Scheduled_End]) && [Event_Ref] <> IDCurrent) ||
      ([Scheduled_End] >= earlier([Scheduled_Start]) && [Scheduled_End] <= earlier([Scheduled_End]) && [Event_Ref] <> IDCurrent)
      ), [Event_Ref])
      return
      if(isblank(_cnt), false(), True())

       

       

      but its still not working correctly

       

       

      as R1088910  rusn from 19/11 to 3/12 i would expect the 4 above that to be true.