Forum Discussion
Comparing two dates using one calendar table and USERELATIONSHIP
Hi Cahaba,
Thanks for your reply. Sorry for the confusion - yes, your interpretation is correct.
I have two tables (1 fact table, 1 calendar table date dimension).
The fact table has two date IDs ([estimate delivery date id] (int), [delivery date id] (int)) which both get their date infomtion via the calendar table dimension. (The two date ids are related to the calendar table using one active relationship [delivery date id] and one inactive relationship [estimated delivery date id]). In other examples I've read, people have used USERELATIONSHIP() to relate a single calendar table to multiple date fileds.
Without creating a calculated column in the fact table, I'd like to create a measure in the fact table that evaluates a count of orders (rows) where the [delivery date] >[estimated delivery date] for any date period.
Hope that makes more sense! :)
Pbix
part of my question was whether that Date ID was sequential.
if so - then you don't even have to join to the Dim Date table - you can just compare the relative values in Table 1.