cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Filter slicer to capture anything between a start and end date

I have a table with a Epic Key, Start Date and End Date

I need a slicer by Quarter and Year that will show all Epic Keys that fall in the Quarter Year.  I am not sure how to make that work since I need the slicer to show me anything that would fall in that quarter based on the epic duration.

So to simplify the data:

Epic Key    Start Date     End Date

Epic 1         1/1/2020     2/22/2020
Epic 2         2/1/2020     10/31/2020
Epic 3         7/1/2020     7/31/2020

If I select Q1 2020    - I should see Epic 1 and 2
If I select Q3 2020 - I should see Epic 2 and 3

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

I create a sample that using the calculated columns of Quarter and a measure to implement it. Please try and check if it is what you want.

• Create calculated columns of Quarter
``````Quarter start = 'Table'[Start Date].[QuarterNo]
Quarter end = 'Table'[End Date].[QuarterNo]``````

• Create a measure.
``````Measure =
var a = SELECTEDVALUE('CALENDAR'[Quarter])
var b = CALCULATE(MAX('Table'[Start Date]),FILTER('Table','Table'[Quarter start]=a || 'Table'[Quarter end] = a))
return
IF(ISFILTERED('CALENDAR'[Quarter]),b,MAX('Table'[Start Date]))``````

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Frequent Visitor

Do you have a date table related to the data table containing the Epic Key data (dates, etc.)? If so, it's a matter of adding a column to that date table defining each quarter.

Anonymous
Not applicable

I do have a dates table however - can I have relationships to both start and end dates?  My Dates table does have a Quarter column already.

Frequent Visitor

You can have multiple relationships between the two tables, however only one can be an active relationship. Using the "USERELATIONSHIP" DAX command inside a measure or calculated column will allow for the use of the inactive relationships instead of the active relationship.

Anonymous
Not applicable

But for a slicer to capture an epic between the dates is what I need so if I have active and inactive from my dates table to the epic table - I can use the Quarter column from dates as a filter and it will capture the correct data?  I don't have a measure I am just trying to find epics that are live in a quarter.

Community Support

Hi @Anonymous ,

I create a sample that using the calculated columns of Quarter and a measure to implement it. Please try and check if it is what you want.

• Create calculated columns of Quarter
``````Quarter start = 'Table'[Start Date].[QuarterNo]
Quarter end = 'Table'[End Date].[QuarterNo]``````

• Create a measure.
``````Measure =
var a = SELECTEDVALUE('CALENDAR'[Quarter])
var b = CALCULATE(MAX('Table'[Start Date]),FILTER('Table','Table'[Quarter start]=a || 'Table'[Quarter end] = a))
return
IF(ISFILTERED('CALENDAR'[Quarter]),b,MAX('Table'[Start Date]))``````

Best Regards,

Xue Ding

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.