cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Filtering out multiple given date ranges

@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
Highlighted
Super User IX
Super User IX

Re: Filtering out multiple given date ranges

@hunterfeldman , 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-tr...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Continued Contributor
Continued Contributor

Re: Filtering out multiple given date ranges

@hunterfeldman 

 

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.
 
 
Highlighted
Resolver I
Resolver I

Re: Filtering out multiple given date ranges

@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. 

Highlighted
Resolver I
Resolver I

Re: Filtering out multiple given date ranges

@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. 

Highlighted
Continued Contributor
Continued Contributor

Re: Filtering out multiple given date ranges

@hunterfeldman 

 

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

Highlighted
Resolver I
Resolver I

Re: Filtering out multiple given date ranges

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors