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.
Hi CahabaData
Thanks - yes - date ids are sequential so this is something I considered doing. Just two small issues for me -
1) Where the EDD/DD is unknown then the dateid = -1 (so would need some row evaluation to check if dateid <>-1
2) to do similar (But with the actual dates) I tried returning, for each row, the actual date value for each dateid using something along the lines of:
CALCULATE(VALUES(Dim Date[Date]), USERELATIONSHIP(fact[dateid],Dim Date[dateid])
But, for a reason I haven't yet worked out, the above formula didn't always return the correct EDD/DD date for each dateid - does this formula look correct?
Also - just learning how to relate fields together so v keen to learn about doing this via a measure!
Thanks :)
Pbix