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
MattAllington's avatar
MattAllington
Community Champion
9 years ago

Rowwise comparison of columns is very expensive at runtime.  I suggest you simply add a calculated column called "Late" and add a forumula somthing like this

 

=if(FactTable[Del Date] > FactTable[Est Del Date],"True")

 

You can then use this column in your measures.

 

Late orders = calculate(countrows(FactTable),FactTable[Late] = "True")

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Matt, 

 

Thanks for your reply :)

 

Yes, was thinking about adding a helper column to do this (I'd previously read that calc columns are also quite expensive) so this might be the route to go down. 

 

Is it possible at all to do it via a measure? I'm looking to learn how to compare different date values from different tables as I suspect I'll have to do this alot - without always needing to generate a calculated column. Is it possible to compare two actual dates in the related calendar table, where the dates both reference the same calendar table via two individual relationships?? 

 

Thanks!

 

Pbix