Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find Overlapping Dates Records

Hello All,

 

I am newbie to powerbi and i need your guide and suggestions.

 

I have a sample data as below.

 

Task      Resource   Start          End 
Task 1Resource 102-Jul-1926-Aug-19
Task 1Resource 101-Aug-1926-Aug-19
Task 3Resource 227-Aug-1931-Aug-19
Task 4Resource 301-Sep-1925-Sep-19
Task 5Resource 326-Sep-1925-Oct-19
Task 1Resource 201-Aug-1926-Aug-19
Task 1Resource 220-Aug-1931-Aug-19
Task 1Resource 301-Sep-1925-Sep-19
Task 1Resource 315-Sep-1925-Oct-19


Now i am trying to create a column or measure which gives the count as 1

IF Start Date and End Date as getting overlapped even one day with Same Task and resource then the Column or Measure (OverLapped) gives the Count as 1.

 

The output that i am expecting.

I tried the below dax but it was not the expected output.

 

 

Please help.

 

Thanks,

Mohan V.

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • camargos88's avatar
    camargos88
    6 years ago

    Hi Anonymous ,

     

    Try this code:

     

    Overlapping =
    VAR _start = 'Table'[Start]
    VAR _end = 'Table'[ End ]
    VAR _result = COUNTROWS(
    FILTER(
    'Table',
    'Table'[Task ] = EARLIER('Table'[Task ]) &&
    'Table'[Resource ] = EARLIER('Table'[Resource ]) &&
    (
    (_start >= 'Table'[Start] && _start <= 'Table'[ End ])
    )
    ))
    RETURN _result - 1
     
     
     

2 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Try this code:

     

    Overlapping =
    VAR _start = 'Table'[Start]
    VAR _end = 'Table'[ End ]
    VAR _result = COUNTROWS(
    FILTER(
    'Table',
    'Table'[Task ] = EARLIER('Table'[Task ]) &&
    'Table'[Resource ] = EARLIER('Table'[Resource ]) &&
    (
    (_start >= 'Table'[Start] && _start <= 'Table'[ End ])
    )
    ))
    RETURN _result - 1