Slicer with 2 date fields

[lguima]

Hi, 

  I have the following sample:

  Creation_date | Finish_Date | Responsible
  09/01/2022     | 09/05/2022 | John Doe
  09/05/2022     | 09/10/2022 | Mary
  09/10/2022     | 09/15/2022 | Peter

I need to create a slicer to filter the date based on fields Creation_date and Finish_date. The idea is like a OR clause: if the selected date is between Creation_date OR Finish_Date the data must be filtered. 

Based on the data sample, if I choose the Date 09/05/2022 the two first lines should be filtered.

I found many samples using the USERELATIONSHIP function, but I could not get how to use it with my situation. USERELATIONSHIP example are always based on specific measures, but here I need to get all data filtered.

Please, Any ideas?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yalanwu-msft · Accepted solution]

Hi, @lguima ;

You need create another date table.

Date = UNION(VALUES('Table'[Creation_date]),VALUES('Table'[Finish_Date]))

Then create a flag measure.

flag = IF(MAX('Table'[Creation_date]) in VALUES('Date'[Creation_date])||MAX('Table'[Finish_Date]) in VALUES('Date'[Creation_date]),1)

Then setting it.

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jthomson]

What you might need to do is create a separate table which has the primary key of this table, and then expands every date between your creation date and finish date fields into a new row (i.e. for your first line, there would be five rows with each of the first five days of this month in them). Relate that to your main data table, and have the slicer look at the new table. This should then allow you to get what you want