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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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
v-rzhou-msft
Community Support
Community Support

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
Not applicable

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

SergioSilvaPT
Resolver V
Resolver V

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

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/
Anonymous
Not applicable

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/
Anonymous
Not applicable

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
Resolver V
Resolver V

Hi @Anonymous ,

 

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/
Anonymous
Not applicable

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.