Skip to main content
cancel
Showing results for
Search instead 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
Advocate I

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.

Advocate I

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.

## Helpful resources

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors