Dedicated date table for each date column in a dimension table?

[mbbozzuto]

I have a store table with two separate date columns. I want to use both date columns for filtering over different time ranges.

For example, my main dimension table is 'Store'.

Store has [Open Date] and [Close Date] columns where each store in the list has its own open and close dates. I want to connect 'Store' to date tables so that I can filter the list over different date ranges; each date table has columns that provide different date formatting (Q#, MM-YYYY, etc.).

I want to be able to filter Store in a way where I could return, as an example, all of the stores with an [Open Date] in Q1 2020 and a [Close Date] in 12-2022.

Is the best practice here to have two separate copies of the date table in the model with 'Store'[Open Date] and 'Store'[Close Date]' each having a relationship to their own unique table so each can be filtered independently?

P.S. my actual problem involves more than two date columns, so trying to determine whether each date column having its own date table is overkill. I have used inactive relationships between different date fields to a single date table, but then that involves accessing the filtered conditions through measures, which adds complexity and reduces flexibility.

[DataNinja777]

@mbbozzuto 

In this kind of task, I would recommend you to use the calender table as a disconnected table instead of creating two calendar tables with relationships with fact tables.  One of many amazing things about dax is its ability to flexibly deal with the time dimension.  By not creating a relationship, and just leaving it as a disconnected table, you can write a measure like below to produce the number of open stores which repects the time dimension. 

[mbbozzuto]

@DataNinja777 thanks for the example DAX but that doesn't address the example I gave where the user is trying to select stores that fall within two separate date ranges with one range applying to one date column and the second range applying to the other date column.  To write this in DAX, if possible given the need to select two separate date ranges that apply separately, would only be possible by at least a moderately experienced DAX professional, so not something accessible to the average business user trying to find their own solution quickly.

In Power BI, they could filter the columns directly on the table (this does not appear to be possible though when connecting to the Dataset through Excel), but this doesn't address formatting needs if the dates need to appear in a certain format, or as Q#, or FY##, which are accessible only through a connected date table.

[DataNinja777]

Hi @mbbozzuto 

In fact, I used the measure like above for headcount analysis and it appears that the same technique can be applied to the open stores analysis which you require.  Even using the disconnected calendar table, we can analyse headcount (and open stores) using the fields like Q#, or FY##, and so on contained in the disconnected calendar table. The measure above would be able to express the time duration of open stores like the duration of employee's tenures which is shown in the matrix visualization below:

[mbbozzuto]

@DataNinja777 I'm just trying to understand; I'm not sure that example is the same date filtering.  I'm trying to simply filter 'Stores' to return those rows where 'Stores'[Open Date] is in Q1 2020 ([Open Date] >= '1/1/2020 AND <= 3/31/2020) and 'Stores'[Close Date] is in 12/2022 ([Close Date] >= 12/1/2022 AND <= 12/31/2022).

A store row that has [Open Date] in 12/2022 should be EXCLUDED.

How do you handle the need for two separate selected date ranges?

[DataNinja777]

Hi @mbbozzuto 

If you have a fake data which you can share, I will try to tackle your request.