Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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:
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.
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
Thanks. I'm attaching a sample screenshot of what the data looks like and what exceptions I'm looking for.
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |