Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Comparing two dates using one calendar table and USERELATIONSHIP

Hi All,    I want to compare two dates in a single measure to return a count of where delivery date > estimated delivery date within a period.   I have two tables:   1) Fact Sales ([Order ID], ...
Need Help
CahabaData's avatar
CahabaData
Memorable Member
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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