I am using Power BI Dataset (SSAS) / DAX but I think this is a general BI question. 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.
Hopefully this is a good example of why you want only one calendar table... Say I create a simple measure, like Total Sales Dollars := SUMX('SalesFact',[Quantity] * RELATED('DimProduct'[UnitPrice]))
and then I want to compare to the previous year. If I have the explicit measure, it's a piece of cake:
CALCULATE( [Total Sales Dollars], SAMEPERIODLASTYEAR('DimDate'[Date])) ... but it only works if you have the correct date dimension table in your SAMEPERIODLASTYEAR() function. If you don't, you get an unfiltered total with the same value for every time period in your visual.
Say you have your measure, and you drop it into the Values section of a matrix visualization. If you have multiple calendar tables, which one do you use when? if you choose the wrong one, you'll get the standard list of dates/time units on one axis, but all the values will be the same, because the filtering happens through the relationship.
You can have multiple calendar tables in your model, but it gets confusing. If you use USERELATIONSHIP(), you can have ONE calendar table and do running sums etc based on ShipDate, OrderDate, ManufactureDate etc (whatever date columns you have in your fact table) and you don't have to change your model. I would swear Adam Saxton (from GuyInACube) did a video on Calendar tables and how PowerBI deals with a data model where no table is marked as a date/calendar table, because I'd swear it's where I learned it. Check the GuyInACube YouTube channel - there are several on dates.