Filtering by dates in two columns

[Anonymous]

I have a set of data that looks like the below table.  As you can see it has two date columns, the settlement (when it was bought) and the maturity (when it was sold).

I would like to be able to use a slider or some other user friendly dashboard tool so that a user can filter by week and see ALL the activity (bought & sold) for that week.  For instance, in this data there are 6 purchases and 6 sales for the week 5/15/22 through 5/21/22.

Is there a way to set up the data so that a slider can be used to show all the activity (settlement & maturity) for an inputted week?

Issuer	Settlement	Maturity	Face Value	Cost
TGT	4/22/2021	5/15/2022	$            2,536,000	$         2,614,877
X	4/20/2022	5/16/2022	$          14,500,000	$       14,494,973
BAC	5/2/2022	5/16/2022	$          17,000,000	$       16,995,042
WMT	5/3/2022	5/16/2022	$          10,000,000	$         9,997,364
MCD	4/18/2022	5/17/2022	$          10,000,000	$         9,992,750
BBUY	2/11/2022	5/18/2022	$            4,965,000	$         4,957,056
PM	5/16/2022	7/8/2022	$          41,000,000	$       40,933,603
PFE	5/17/2022	6/28/2022	$          10,000,000	$         9,990,083
VZ	5/18/2022	6/1/2022	$          30,000,000	$       29,987,983
X	5/18/2022	6/29/2022	$          20,000,000	$       19,980,167
MCD	5/18/2022	7/22/2022	$          20,000,000	$       19,959,556
TWTR	5/19/2022	6/10/2022	$            8,000,000	$         7,994,720

Thank you in advance for your help

[v-rzhou-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou

[Anonymous]

I do not know how to send a file through this website.

[SergioSilvaPT]

@Anonymous i think i understand what you need, but for this you will have to use a Calendar table.

And you will have to create a copy of your data table, like the model in example:

You have a Calendar Table and then Calendar A and Calendar B that are reference Calendar, you have go to modeling and click New Table and the you create:

Calendar A = 'Calendar' 

Calendar B = 'Calendar' 

Table (A) = 'Table (B)'

Table (A) is your actual data table.

On Table (A) create the measure:

Cost A = CALCULATE(
    SUM('Table (A)'[Cost]),
    KEEPFILTERS('Calendar A'[Date]),
    ALL('Calendar'[Month])
    )

On Table (B) create the measure:

Cost B = CALCULATE(
    SUM('Table (B)'[Cost]),
    KEEPFILTERS('Calendar B'),
    ALL('Calendar'[Month])
)

Then you can recreate the connections to model as shown above.

You will be able to achieve what you want:

Regards,

Sérgio Silva 

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

[Anonymous]

Unfortunately I'm not able to recreate what you've done.

• is there supposed to be a [Date] after 'Calendar B' in the below code?

  Cost B = CALCULATE(
      SUM('Table (B)'[Cost]),
      KEEPFILTERS('Calendar B'),
      ALL('Calendar'[Month])
  )

• what are the relationships betwen calendar A & Calendar, and Calendar B & Calendar?  are you linking date to date in both of these?

[SergioSilvaPT]

Yes, as you can see in the photo of the model you should connect then all with the date column, but it's important to keep the relationship direction.

Tell me if you need further help.

[Anonymous]

I think there is a misunderstanding, but I agree with your point regarding OR.  The synching of slicers is causing less results to show up when my end goal is to have more.  In the above data, when I synch slicers and enter date range 5/15/22 to 5/21/22, nothing shows up.  I want everything to show up.  

Currently, the maturity date is the field that has a relationship with my calendar table.  The ideal solution is one slicer to enter a date range, and it shows all data that has a maturity date within that range OR a settlement date within that range.

[SergioSilvaPT]

Hi @Anonymous ,

You can do the following:

Create two slicers based on each date column.

Then select the first slicer and go to View and click on Sync Slicers, and the Advanced options of the Sync slicers pane write some name like Date.

Now, click on the other slicer and give the same name.

You can check that if you move a slicer the other move accordingly.

After that, and with the second slicer selected go to View -> Selection and click to hide the second slicer.

It's done!

To have it by week, do you have a calendar table in your model? If not You can add two calculated columns to the table with the data, one Week 1 based on settlement date and other Week 2 based on the maturity date.

Week 1 = "Week " & WEEKNUM(table[settlement])

Week 2 = "Week " & WEEKNUM(table[maturity])

And use this new columns in the slicers and do the same process to sync them!

Regards,

Sérgio Silva 

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

[Anonymous]

Thanks Sergio, I synched the sliders and they do interact with eachother.  The issue is that when both slicers are have the same week range selected, it looks for rows that have both a settlement and maturity within that week, but there are none.  

I would like to have the slicer operate more as an AND type operator: "show maturities within 5/15/22 to 5/21/22 AND settlements between 5/15/22 to 5/21/22"

Does any of that make sense?

[SergioSilvaPT]

The AND type operator is what both slicers are doing, maybe you want OR...

The example that you gave you're selecting the same date range, so it must be true for both dates, it's maturities date AND settlements date.

If you want OR you have to show both slicers and deactivate the sync between them.

Or did i understand incorrectly?