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
Anonymous
Not applicable

Filtering out multiple given date ranges

I am trying to calculate the total hours employees worked. There are special exceptions where the employees hours should not be considered in the calculation. 

The exceptions table looks like table below. I do not want to sum the days between the start and end dates, and if there is no end date we will not sum any days between the start date and the current day 

IndexTeamEmployee IDStartEnd
1XA8/1/20208/3/2020
2XB8/5/2020 
3XA8/5/2020 
4YC8/4/20208/8/2020
5ZD8/1/2020 

 

My sample hours data looks like the following:

 

DateTeamEmployee IDHours
8/1/2020XA8
8/1/2020XB7
8/1/2020YC4
8/1/2020ZD8
8/1/2020ZE10
8/2/2020XA4
8/2/2020XB6
8/2/2020YC7
8/2/2020ZD9
8/2/2020ZE10
8/3/2020XA2
8/3/2020XB5
8/3/2020YC1
8/3/2020ZD4
8/3/2020ZE10
8/4/2020XA5
8/4/2020XB6
8/4/2020YC8
8/4/2020ZD3
8/4/2020ZE10
8/5/2020XA7
8/5/2020XB6
8/5/2020YC8
8/5/2020ZD4
8/5/2020ZE10
8/6/2020XA6
8/6/2020XB8
8/6/2020YC9
8/6/2020ZD4
8/6/2020ZE10
8/7/2020XA6
8/7/2020XB8
8/7/2020YC9
8/7/2020ZD4
8/7/2020ZE10
8/8/2020XA2
8/8/2020XB5
8/8/2020YC6
8/8/2020ZD7
8/8/2020ZE10

 

My expected results are as follows: 

 

DateTotal Hours 
8/1/202029
8/2/202032
8/3/202022
8/4/202032
8/5/202022
8/6/202014
8/7/202010
8/8/202010

 

The relationships between the tables:

 

hunterfeldman_0-1598541118450.png

 

What the locations table looks like: 

 

TeamLocation 
XMars
YEarth
ZVenus
 
 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@mhossain

Figured out the solution based on your suggestion. Used power query and added a custom column, [Full Date List] with the following M code: 

let 
_endDate = if [End Date] = null then Date.From(DateTime.LocalNow()) else [End Date],
_startDate = [Start Date],
_endNum = Number.From(_endDate),
_startNum = Number.From(_startDate)
in {_startNum.._endNum})

This generates a list of dates between the start and end date by leveraging the '..' operation 

After doing this I expanded the list which will add a row corresponding to each employee and exception date. 

From here I merged the 'Hours' and 'Exceptions' table based on two columns from each table: 
'Hours'[Employee] and 'Hours'[Date] with 'Exceptions'[Employee] and 'Exceptions'[Full Date List] 

View solution in original post

6 REPLIES 6
mhossain
Solution Sage
Solution Sage

@Anonymous 

 

Please see below steps:

 

1) Go to PowerQuery, in your "Hours" table apply 'Merge' and join with "Exception" table as per the Date columns. Use left merge join

2). Now expand, and displace 'Start' and 'End' date two columns from Exception table to your 'Hours' Table.

3). Create a custom column like below 

Criteria = if [Exception.Start] <> null and [Exception.End] = null then "Filterout" else "Ok"

 

4). Close and apply powerquery window, go to your powerbi dax environment and create a measure like below:

Total Hours = CALCULATE(sum(Hours[Hours]),Hours[Criteria] = "OK")
 
And this should work like charm, if above doesn't work then you might are missing something or need to adde more small steps.
 
Please let me know if above is making sense.
 
 
Anonymous
Not applicable

@mhossain 

I'm not seeing how the merge between 'Hours' and 'Exceptions' would work. If I merge based on 'Hours'[Date] and 'Exceptions'[Start Date] then it would only expand the data for a single day (the start date), not for all of the dates between start and end.

This does give me an idea though, thanks. 

@Anonymous 

 

I understand, actually missed the in between part, you are right.

Anonymous
Not applicable

@mhossain

Figured out the solution based on your suggestion. Used power query and added a custom column, [Full Date List] with the following M code: 

let 
_endDate = if [End Date] = null then Date.From(DateTime.LocalNow()) else [End Date],
_startDate = [Start Date],
_endNum = Number.From(_endDate),
_startNum = Number.From(_startDate)
in {_startNum.._endNum})

This generates a list of dates between the start and end date by leveraging the '..' operation 

After doing this I expanded the list which will add a row corresponding to each employee and exception date. 

From here I merged the 'Hours' and 'Exceptions' table based on two columns from each table: 
'Hours'[Employee] and 'Hours'[Date] with 'Exceptions'[Employee] and 'Exceptions'[Full Date List] 

amitchandak
Super User
Super User

@Anonymous , the second table you can join directly with date. First table either you should not join or join os start date.

You can refer how to get information from first table from this blog. check the current employee.

 

You can join both tables with employee common table

 

heck: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

 

Anonymous
Not applicable

@amitchandak 

Hmm I can see what you're doing in the article but I'm not exactly sure how it translates into the problem I have here. I understand having two relationships between the 'Date' table and 'Exceptions' table (one for Start Date, one for End Date) so that we can use the USERELATIONSHIP() function. 

The discrepancy is that in the article you are only counting the single 'Employee' table, which is indexed and has unique start/end dates for each employee. The problem I'm going to run into is that my 'Exceptions' table will have both multiple employee entries and employees with multiple start/end dates, plus when I link this to the 'Hours' table it will create a many-to-many relationship. 
Additionally, the 'Exceptions' table does not include the entire list of employees, only those that need to be not included. 


I guess I'm just not seeing how the solution in your article will handle the an employee that has multiple start and end dates. 

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.