Refining Date Slicer for Multiple Table Filtering

[phistudio987]

Configuring the Date Slicer in Power BI to facilitate proper filtering in two tables presents a challenge in my current dataset, where the Company and Deals tables maintain a 1:N relationship. At present, I've implemented a Year and Month Slicer using the "Create Date" field from the Company table, successfully filtering the Deals table based on this field through matching connections.

However, my specific goal is to expand this filtering to the Company table, utilizing the "Create Date" field from the Company table and the associated "Close Date" field exclusively from the Deals table. To address this, I pursued the following approach:

1. I generated date entries covering the entire range between the minimum and maximum date values from the Company table's "Create Date" field.

2. This resulted in a single column of dates. Subsequently, I connected the Company table's "Create Date" field with the Date field, and similarly, I connected the Deals table's "Close Date" field with the Date field.

While I've followed this approach, I am unsure if it is the correct method. I had hoped for a more straightforward solution but have not found one yet.

I am seeking guidance to validate and enhance this approach, and I appreciate any assistance to ensure the date slicer seamlessly works across the desired data fields. Thank you for your support.

[phistudio987]

Thanks for the clarification!

[amitchandak]

@phistudio987 , to me seem like you need very similar approach like HR analytics , Active, Created and closed measures

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[phistudio987]

Thank you for your suggestions. I think the solution you suggested is something I have tried already but that is not I was looking for.

Do you think that there is no other way to do this? I was trying to find some simple and elegant way to doing this rather than creating a separate Date table.

I wonder if creating Date table is the only way to doing this in Power BI.

[v-jingzhan-msft · Accepted solution]

Hi @phistudio987 

Creating a Date table in the model and connecting it to fact tables is not the only way but it is an elegant way. As the date table has continuous and distinct dates in a date range, using it to filter other fact tables can avoid missing any date. If we use a date column from a fact table for filtering other tables, it might miss some dates if those dates are not existing in that fact table. 

Also using a date table has many other advantages, e.g. it can make time intelligence calculations easier. That's why we usually recommend having a date table. You may learn more from Do You Need a Date Dimension? - RADACAD

In addition, what you did actually implements the star schema model concept to some extent. Star schema is also recommended when modeling. 

Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

Power BI – Star schema or single table - SQLBI

So be confident in what you have made. For refining it, you can connect the date table to the Deals table too. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!