Filter Multiple Date Columns in One Slicer - What am I doing wrong?

[SirJJAnderson]

I'm trying to create a sales stage by lead source report that I can filter by month, quarter, etc.

I followed the solution offered here https://community.powerbi.com/t5/Desktop/Filter-Multiple-Date-Columns-in-One-Slicer/td-p/669410 but I must have done something wrong because I'm seeing data outside my date filter.

For March 2020, I should be seeing 43 deals according to CRM, but I'm actually seeing a count of 53. Upon further investigation, there are 10 deals displaying even though the Create Date is before March... not sure what I did wrong??

I'm a Power BI noob... literally just started last week and have no idea what to do next to troubleshoot. Any help would be greatly appreciated!

[Anonymous]

Hi @SirJJAnderson ,

May I know whether the filter field "Date" and "Earliest createdate" is from the same field? Which fields the visuals in your screen shot be created based on? Are these fields from different tables? Is there any relationship be created among tables?

It is better if you can provide us your sample PBIX file (exclude sensitive data)? Then we can make troubleshooting to find the cause of why can't get the correct number of records. Thank you.

Best Regards

Rena

[SirJJAnderson]

@Anonymous  The Date, createdate, demo_date and hs_date_entered_612408 are all different. The last 3 represent timestamps of when an opportunity entered different stages in our sales pipeline, and the "Date" field is from the date table that I created for the slicer.

The Deal and Deal Date Data Transformed tables are related via a unique identifier "Deal ID".

Happy to share the pbix file but how do I exclude any sensitive data? I've only sent a pbix file once so far and am not really sure how it works as far as the dataset is concerned.

[Anonymous]

Hi @SirJJAnderson ,

It is hard to figure out what's the cause of problem without the related table structures and relationships among the tables. Could you please provide these information? And if it is convenient, could you please create another report file with sample data which include same tables, relationships and visuals with the original one? Then share it with me. Thank you.

Best Regards

Rena

[Anonymous]

Hi @SirJJAnderson ,

I just updated your report file, please check if that is your desired result. You can get it from this link. The parts marked with red circle/line is what I updated...

Best Regards

Rena

[SirJJAnderson]

@Anonymous  unfortunately that isn't right 😞 the create date column total is correct (43) but now the demo date column is wrong (should be 53). I can't use any one date column as the slicer because it will filter the other columns incorrectly. I essentially need to be able to filter each date column by its own date and nothing else.

We had 43 deals created in March, but 53 demos were held (10 were from earlier in the year Feb, Jan, etc.) and I need to be able to report column totals for all March dates and March dates only.

That's why I tried the solution here https://community.powerbi.com/t5/Desktop/Filter-Multiple-Date-Columns-in-One-Slicer/td-p/669410

[SirJJAnderson]

Here's a screenshot of the relationship:

The Date, createdate, demo_date and hs_date_entered_612408 are all different. The last 3 represent timestamps of when an opportunity entered different stages in our sales pipeline, and the "Date" field is from the date table that I created for the slicer.

The Deal and Deal Date Data Transformed tables are related via a unique identifier "Deal ID".

So this is a dumb question... how do I exclude any sensitive data? I've never sent a PBIX file before 🙂

[Anonymous]

Hi @SirJJAnderson，

As checked the screen shot in your original post, it seems you are counting the number of deals by the field createdate, but the filter fields applied on visual is Date field in table Date... The prolem should be here. Please try to change page-level filter field from "Date" to "createdate" field.

Best Regards

Rena

[SirJJAnderson]

I've tried that, it also filters the other two date-based columns. My goal here was to create a date slicer that would filter each of the date-based columns to their specific count in that particular date range. Basically, each column needs filtered by it's own date range, but that's not possible without creating a date table, etc.

[sanimesa]

@SirJJAnderson   Please check your model - there should be a one to one relationship between the data dimension table and the date in your fact table that you want filtered. Looking at your diagram, have you connected the Earliest demo date instead?