Surfacing values between two dates based on selected value...

[mpml]

Hi, I have the following basic tables:

Table 1:

Title | Release Date

Table 2:

Release Date | Corridor Start Date | Corridor End Date

Release Dates are obviously connected by a 1 to many relationship. I want to be able to use a slicer to select 1 Title, but then in a visual I want to surface any Title that has a Release Date between Corridor Start Date and Corridor End Date.

I'm trying to create a measure that would flag a Title if it shows up between the Start and End Date values, but I'm struggling to do so. I believe if I can do that then I could edit interactions for the original Title slicer to not filter the new visual, and instead use the measure as a filter in the new visual.

Any help is appreciated!

[AllisonKennedy]

@mpml  You don't need a relationship between the two tables, since you're looking for dates that fall between the range, you need an approximate lookup. See if this blog helps: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html Instead of Min and Max Sales, you'll use Corridor Start Date and Corridor End Date. 

Also, for future data models, always use a date table for dates: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

[mpml]

I'm not sure if I explained myself all that well - but I appreciate the links! Do you mind reading my clarifications and seeing if you have any additional resources or suggestions for me?

My goal is to have a bar chart with TITLE on the x-axis, and PERCENT VALUE as the bars. But I only want a TITLE to surface if it has a RELEASE FRIDAY (date) between CORRIDOR START and CORRIDOR END of the TITLE release date.

I have a slicer set up where users select a single TITLE so as to surface specific key metrics on this page in other visuals related to that specific selected TITLE. 

Tables:

TITLE | RELEASE FRIDAY (so a bunch of titles with matching release fridays, we have a bunch of other meta data columns in here as well but trying to keep it simple)

TITLE | PERCENT VALUE (this is a separate table because we actually have multiple metrics in here so we don't want to have to repeat all the meta data from the other TITLE table)

RELEASE FRIDAY | CORRIDOR START | CORRIDOR END (perhaps this is unnecessary... could I potentially use the filter function to only return TITLEs if RELEASE FRIDAY is between the selected RELEASE FRIDAY (via TITLE slicer) minus 8 days and RELEASE FRIDAY plus 8 days?)

So essentially, there are multiple TITLES for each RELEASE FRIDAY. User selects one TITLE via a slicer, and the bar chart would update to show every TITLE (including selected) with a RELEASE FRIDAY either 1 week before, on the same day, or 1 week after.

[v-deddai1-msft]

Hi @mpml ,

Would you please show us some sample data by onedrive for business? I can't understand your well just through your long string of text descriptions. We need some sample data to help us understand your requirement. 

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

Best Regards,

Dedmon Dai