Check if Date from a slicer is within a range of two date fields

[taylorpeter55]

Hi,

I have a slicer in my dashboard as outlined in red below and a table with Range Start and End Dates (Second screenshot). 

Does anyone know if there is there a way of filtering the rows in my table based on whether the Range selected in the slicer has any crossover with the Start & End Dates range in my table? 

 

In other words, all but the row highlighted in yellow would be included in the filter. The yellow row would be excluded because the Project ended before the lower date selected in the slicer.  

I've trawled through the forum and although there are many similar discussions, i've not found one that works for my particular predicament. 

Thank you in advance for any assistance you can provide. 

Pete

[PhilipTreacy · Accepted solution]

Hi @taylorpeter55 

NOTE: You posted in the PQ fourm but you can't do this in PQ. If you are using slicers you need to use DAX. I moved this to the Desktop forum.

Download example PBIX file

I've used a disconnected Date Table for this.  The slicer is gettign its dates from this table.

Get the earliest (MIN) date from the slicer

MinStartDate = CALCULATE(MIN('DateTable'[Date]))

and the latest (MAX) date from the slicer

MaxEndDate = CALCULATE(MAX('DateTable'[Date]))

Create a measure that checks for overlap between the dates selected in the slicer, and your data.  If there is, assign a value of 1, else 0.

ShowRecord = IF(SELECTEDVALUE('DataTable'[Start Date]) > [MaxEndDate] || SELECTEDVALUE('DataTable'[End Date]) < [MinStartDate], 0, 1)

The logic here is that a project End Date must be after its Start Date.  So you just need to check if the Start Date is later than the max date, and if the End Date is earlier than the min date.

Use this measure as a filter on your table.  Only shows records where the measure is 1.

Regards

Phil

[taylorpeter55]

Hi @Ashish_Mathur, @PhilipTreacy 

I've had a look at your solutions and they are both very similar - and work great :-). Thanks so much. 

I do have a further question if you don't mind please. 

The date table that I have created is based on the date range within my single data source (let's call it data source A).

However, when trying to apply this to my data, I realised that I have two different data sources, both with different date columns. 

Is it possible to use the slicer created in your example above to filter on the separate date columns in the two different date sources?  

The reason I ask is that in the below screenshot, the 'active projects in portfolio' card is from data source A (Start Date and End Date in previous example we worked), however, the 'Using Automation' card is from data source B - this data source only has one date column called Execution Date. 

I was wondering if it is possible to use the Min/Max dates from your earlier solution to count rows in data set B where the Execution Date is between the Min/Max dates?

Hopefully i've explained that ok but please let me know if any further questions. 

Thanks again for your help - I really appreciate it. 

Pete 

[taylorpeter55]

@Ashish_Mathur @PhilipTreacy After a little bit more digging i found the Sync Slicer option in desktop. Problem solved 🙂

Thanks so much for your help. 

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[taylorpeter55]

@Ashish_Mathur Thanks so much for sharing your solution. I will review this today and report back 🙂

[PhilipTreacy · Accepted solution]

Hi @taylorpeter55 

NOTE: You posted in the PQ fourm but you can't do this in PQ. If you are using slicers you need to use DAX. I moved this to the Desktop forum.

Download example PBIX file

I've used a disconnected Date Table for this.  The slicer is gettign its dates from this table.

Get the earliest (MIN) date from the slicer

MinStartDate = CALCULATE(MIN('DateTable'[Date]))

and the latest (MAX) date from the slicer

MaxEndDate = CALCULATE(MAX('DateTable'[Date]))

Create a measure that checks for overlap between the dates selected in the slicer, and your data.  If there is, assign a value of 1, else 0.

ShowRecord = IF(SELECTEDVALUE('DataTable'[Start Date]) > [MaxEndDate] || SELECTEDVALUE('DataTable'[End Date]) < [MinStartDate], 0, 1)

The logic here is that a project End Date must be after its Start Date.  So you just need to check if the Start Date is later than the max date, and if the End Date is earlier than the min date.

Use this measure as a filter on your table.  Only shows records where the measure is 1.

Regards

Phil

[taylorpeter55]

Hi Phil, Thanks so much for sharing your solution and the detailed description. I will review this today and see if I can apply this to my dashboard. I'll let you know how i get on 🙂