Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgsem
Regular Visitor

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

1 ACCEPTED SOLUTION

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:

Annotation 2020-02-25 100205.png

 

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:

Annotation 2020-02-25 100346.png

 

For the related .pbix file,pls click here.

 

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

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

That's a good question. The answer is not knoweable without sample data and expected output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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])

 

@amitchandak Do I just use the formula on another column, or do I create a new measure for this ?  Do I need to separate tables for employees and their ID no, work dates and leaves, or would this work in the same table as above ?

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:

Annotation 2020-02-25 100205.png

 

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:

Annotation 2020-02-25 100346.png

 

For the related .pbix file,pls click here.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.