Forum Discussion

mgsem's avatar
mgsem
Regular Visitor
6 years ago
Solved

How to compare two date ranges

Hi,

 

I'm just a beginner in using Power BI. I have a dilemma in comparing two dates , one for leaves and another for scheduled work. The objective is to see if there was any overlap between the two dates. 

 

Complicating things are: the leaves are categorized by type (e.g. sick leave, vacation leave, etc) and may have 2 or more date ranges (start and end dates) across the sample month. Work schedule is irregular and may just be once or 5 times a week. And this assessed per employee (which numbers around a thousand).

 

How would Power BI compare the two tables and see if there were any overlapping date (or common dates between) on leaves and work schedule ? 

 

Thanks

Need Help
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi mgsem ，

     

    You'd better use a calculated column as below:

     

    Remark = 
IF(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Id] = EARLIER('Table'[Id]) && 'Table'[Start ]<= EARLIER( [Work dates ]) && 'Table'[End]>= EARLIER( [Work dates ]) && NOT(ISBLANK('Table'[Start ]) || ISBLANK('Table'[End]))))>0,1,BLANK())

     

    And you will see:

     

    Also you can create a measure as below：

     

    Measure = IF(CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[Id] = SELECTEDVALUE('Table'[Id]) && 'Table'[Start ]<= SELECTEDVALUE('Table'[Work dates ]) && 'Table'[End]>=SELECTEDVALUE('Table'[Work dates ])&& NOT(ISBLANK('Table'[Start ]) || ISBLANK('Table'[End]))))>0,1,BLANK())

     

    And you will see:

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!
     

5 Replies

    • mgsem's avatar
      mgsem
      Regular Visitor
      6 years ago

      Thanks. I'm attaching a sample screenshot of what the data looks like and what exceptions I'm looking for. 

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        One of the following should work, if count >0, is your flag

        flag = countx(filter(table,table[Workdates]>=earlier(table[start]) && table[Workdates]<=earlier(table[start])
					&& not(isblank(earlier(table[Leave Type])))),table[Employee No])
or 

flag = countx(filter(table,table[Workdates]>=earlier(table[start]) && table[Workdates]<=earlier(table[start])
					&& not(isblank((table[Leave Type])))),table[Employee No])