Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
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

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]

4.PNG 

  • 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]))

5.PNG

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.

View solution in original post

5 REPLIES 5
MDrabik
Advocate I
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.

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.

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]

4.PNG 

  • 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]))

5.PNG

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.