Current week date filter applied to all days between two dates

[SHenderosn]

I'm trying to add a slicer for filtering down PTO requests that fall within the current week, but I can't get it to filter correctly. 

My data set is pretty simple and includes a start and end date for each PTO request.  I also have full calendar/date table with a "CurrentWeekOffset" column that I use for filtering. There is a relationship between the PTO Start Date my date table.

The problem, is that when I try to filter to a CurrentWeekOffset of "0" (i.e., date falls within current week), it only looks at the related Start Date and hides any PTO requests that may have started last week but are ongoing this week.  

Full Data Set:

Employee	Start Date	End Date
Justin	3/28/24	4/9/24
John	3/29/24	4/5/24
Deja	4/1/24	4/5/24
Benjamin	4/3/24	4/8/24
Latisha	4/15/24	4/23/24
Melissa	4/29/24	5/7/24
Carie	5/15/24	5/22/24

What it currently shows for Current Week as 3/31/24-4/6/24:

Employee	Start Date	End Date
Deja	4/1/24	4/5/24
Benjamin	4/3/24	4/8/24

What it should show:

Employee	Start Date	End Date
Justin	3/28/24	4/9/24
John	3/29/24	4/5/24
Deja	4/1/24	4/5/24
Benjamin	4/3/24	4/8/24

In this example, Justin and John's PTO start dates were last week, but they are still on vacation this week and should show up when I filter it to current week PTO.

How do set up my data and relationships so that when I filter to Current Week, it shows all rows where there is any day between Start and End Date that falls in the current week? 

[TheoC · Accepted solution]

Hi @SHenderosn 

You should be able to achieve this by adding a calculated column in your PTO table that checks whether the specific record is in the current week or not. This column can then be used as a Slicer to filter whether a PTO falls within the Current Week. 

Just update the below to your table and column names and it should work well.  You can then add this as a slicer and achieve your desired output. 

PTO_In_Current_Week = 

VAR _start = 

	CALCULATE ( 
		MIN ( 'DateTable'[Date] ) , 'DateTable'[CurrentWeekOffset] = 0 
		)

VAR _end = 

	CALCULATE ( 
		MAX ( 'DateTable'[Date] ) , 'DateTable'[CurrentWeekOffset] = 0 
		)

RETURN

IF (
    AND (
        'PTOTable'[Start Date] <= _end , 
        'PTOTable'[End Date] >= _start ) ,
    "Current Week" , "Not Current Week"
	)

)

Hope this works okay!

Theo

[TheoC]

@SHenderosn also, there is a really great post by @v-jiascu-msft (Dale) that provides a very simple solution to achieve your desired output as well in the form of a calculated column.  You can refer to it here:

https://community.fabric.microsoft.com/t5/Desktop/Determine-if-date-is-between-2-dates/m-p/314685

Again, you can use the calculated column as a Slicer to achieve the requirements you are after and adjust the output in Dale's solution.

Have a great day and all the best!

Theo 🙂

[TheoC · Accepted solution]

Hi @SHenderosn 

You should be able to achieve this by adding a calculated column in your PTO table that checks whether the specific record is in the current week or not. This column can then be used as a Slicer to filter whether a PTO falls within the Current Week. 

Just update the below to your table and column names and it should work well.  You can then add this as a slicer and achieve your desired output. 

PTO_In_Current_Week = 

VAR _start = 

	CALCULATE ( 
		MIN ( 'DateTable'[Date] ) , 'DateTable'[CurrentWeekOffset] = 0 
		)

VAR _end = 

	CALCULATE ( 
		MAX ( 'DateTable'[Date] ) , 'DateTable'[CurrentWeekOffset] = 0 
		)

RETURN

IF (
    AND (
        'PTOTable'[Start Date] <= _end , 
        'PTOTable'[End Date] >= _start ) ,
    "Current Week" , "Not Current Week"
	)

)

Hope this works okay!

Theo

[SHenderosn · Accepted solution]

This is exactly what I needed, thank you!

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[TheoC]

Hi @SHenderosn 

You should be able to achieve this by adding a calculated column in your PTO table that checks whether the specific record is in the current week or not. This column can then be used as a Slicer to filter whether a PTO falls within the Current Week. 

You will need to update the table and column names to match your model but hopefully this will work for what you are trying to achieve.  By doing it this way, you should be okay to leave your relationships etc as is.

PTO_In_Current_Week = 

VAR _start = 

	CALCULATE ( 
		MIN ( 'DateTable'[Date] ) , 'DateTable'[CurrentWeekOffset] = 0 
		)

VAR _end = 

	CALCULATE ( 
		MAX ( 'DateTable'[Date] ) , 'DateTable'[CurrentWeekOffset] = 0 
		)

RETURN

IF (
    AND (
        'PTOTable'[Start Date] <= _end , 
        'PTOTable'[End Date] >= _start ) ,
    "Current Week" , "Not Current Week"
	)

)

Hope this works okay!

Theo 🙂