cancel
Showing results for
Did you mean:
Highlighted
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

 Index Team Employee ID Start End 1 X A 8/1/2020 8/3/2020 2 X B 8/5/2020 3 X A 8/5/2020 4 Y C 8/4/2020 8/8/2020 5 Z D 8/1/2020

My sample hours data looks like the following:

 Date Team Employee ID Hours 8/1/2020 X A 8 8/1/2020 X B 7 8/1/2020 Y C 4 8/1/2020 Z D 8 8/1/2020 Z E 10 8/2/2020 X A 4 8/2/2020 X B 6 8/2/2020 Y C 7 8/2/2020 Z D 9 8/2/2020 Z E 10 8/3/2020 X A 2 8/3/2020 X B 5 8/3/2020 Y C 1 8/3/2020 Z D 4 8/3/2020 Z E 10 8/4/2020 X A 5 8/4/2020 X B 6 8/4/2020 Y C 8 8/4/2020 Z D 3 8/4/2020 Z E 10 8/5/2020 X A 7 8/5/2020 X B 6 8/5/2020 Y C 8 8/5/2020 Z D 4 8/5/2020 Z E 10 8/6/2020 X A 6 8/6/2020 X B 8 8/6/2020 Y C 9 8/6/2020 Z D 4 8/6/2020 Z E 10 8/7/2020 X A 6 8/7/2020 X B 8 8/7/2020 Y C 9 8/7/2020 Z D 4 8/7/2020 Z E 10 8/8/2020 X A 2 8/8/2020 X B 5 8/8/2020 Y C 6 8/8/2020 Z D 7 8/8/2020 Z E 10

My expected results are as follows:

 Date Total Hours 8/1/2020 29 8/2/2020 32 8/3/2020 22 8/4/2020 32 8/5/2020 22 8/6/2020 14 8/7/2020 10 8/8/2020 10

The relationships between the tables:

What the locations table looks like:

 Team Location X Mars Y Earth Z Venus

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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]

6 REPLIES 6
Highlighted
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

Proud to be a Super User!

Highlighted
Continued Contributor

## Re: Filtering out multiple given date ranges

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

## 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

## 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

## Re: Filtering out multiple given date ranges

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

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

Announcements

#### Power Platform Community Conference

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

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021