Filter slicer to capture anything between a start and end date

[Anonymous]

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

[MDrabik]

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]

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.

[MDrabik]

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]

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.

[v-xuding-msft · Accepted solution]

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.