cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RBraun
Helper II
Helper II

Filtering by dates in two columns

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?

IssuerSettlementMaturityFace ValueCost
TGT4/22/20215/15/2022 $            2,536,000 $         2,614,877
X4/20/20225/16/2022 $          14,500,000 $       14,494,973
BAC5/2/20225/16/2022 $          17,000,000 $       16,995,042
WMT5/3/20225/16/2022 $          10,000,000 $         9,997,364
MCD4/18/20225/17/2022 $          10,000,000 $         9,992,750
BBUY2/11/20225/18/2022 $            4,965,000 $         4,957,056
PM5/16/20227/8/2022 $          41,000,000 $       40,933,603
PFE5/17/20226/28/2022 $          10,000,000 $         9,990,083
VZ5/18/20226/1/2022 $          30,000,000 $       29,987,983
X5/18/20226/29/2022 $          20,000,000 $       19,980,167
MCD5/18/20227/22/2022 $          20,000,000 $       19,959,556
TWTR5/19/20226/10/2022 $            8,000,000 $         7,994,720

 

Thank you in advance for your help

9 REPLIES 9
RicoZhou
Community Support
Community Support

Hi @RBraun ,

 

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

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

SergioSilvaPT
Super User
Super User

@RBraun 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:

SergioSilvaPT_0-1653408803538.png

 

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:

 

SergioSilvaPT_1-1653409002498.png

 

Regards,

Sérgio Silva 

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

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

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?

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.

Regards,
Sérgio Silva

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/
RBraun
Helper II
Helper II

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
Super User
Super User

Hi @RBraun ,

 

You can do the following:

 

Create two slicers based on each date column.

SergioSilvaPT_0-1653394225429.png

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.

 

SergioSilvaPT_1-1653394318792.png

 

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.

 

SergioSilvaPT_2-1653394436846.png

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.
Regards,
Sérgio Silva

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

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?

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? 

Regards,
Sérgio Silva

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.