Date Range: In what time span where projects open?

[TDIDaniël]

Hi,

I have two date fields: ProjectOpenDate, and ProjectCloseDate. 

I'd like to be able to use a multiple date filter (of two separate ones if that does the trick) that allows me to answer the question:

"What projects were open in between Date1 and Date2?" where users can select Date1 and Date2.

The logic would be as follows.

• Date1 <= ProjectCloseDate
• Date2 >= ProjectOpenDate

So if a project would be opened and closed within the selected frame, it would show up. If a project would be closed one day after Date1 it would be considered open by the filter and therefore show up. 

Any thoughts? 

[Anonymous · Accepted solution]

Hi @TDIDaniël ,

Based on your description, I did a test. Is the following result you want?

measure:

Measure = 

var x1=SELECTEDVALUE('Date 1'[Date 1])

var x2=SELECTEDVALUE('Date 2'[Date 2])

return

IF(

 x2>=MAX('Sheet2'[ProjectOpenDate])&&x1<=MAX('Sheet2'[ProjectCloseDate]),1,0)

then drag this measure to the table visual filter, select value equals to 1.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@TDIDaniël , refer if my HR Blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4

You should be able to select a date range from date table.

[TDIDaniël]

Thanks for this, @amitchandak 

Whils this is very helpful, the goal for me is to have a (or two) date slicers that allow me to actually set two dates, and then show all the employees that were hired in that period of time. So an overlap of the hiring period and the selection period.