Forum Discussion
Date Comparison between two Related Tables
I have searched this forum, but I believe I have a very basic request.
I have shimmied down my data model. I have a fact table (factCase) with a date (TIncision_DT) and a dimension table (dimQuality) with a date (QTProcessorReview_DT) and I want to compare the time between TIncision_DT and QTProcessorReview_DT. The tables are related by a common key Referral_ID. The data flows from the dimension to the fact in a single direction as one-to-many, even though it really is a one-to-one relationship. I would prefer to use a measure, but I could use a column if necessary.
Here is the reduced file. https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EW3rlh1NB1hLlvHnQDwIoWQBWcuRa_SEjBsJSRqZuXeD0Q?e=IidCIe
Any help on a formula/measure would be so helpful. Thank you!
dax, @DateComparisons
Hi dkernen ,
You could use DATEDIFF() function.
https://docs.microsoft.com/en-us/dax/datediff-function-dax
Example:
Measure = DATEDIFF(SELECTEDVALUE(factCase[TIncision_DT]),SELECTEDVALUE(dimQuality[QTProcessorReview_DT]),DAY)Best Regards,
Jay
4 Replies
the one has datetime values the other only had the date no time in it is that correct?
That is correct. But I do have another date/time field in the Quality dim table. The QTQualityReview_DT is a true Date time. I would like to compare the fact table's TIncision to both the ProcessorReview_DT (which is, as you stated, just a date) and also to the QualityReview_DT (which is a true datetime). Thanks for eagle eyes!
Hi dkernen ,
You could use DATEDIFF() function.
https://docs.microsoft.com/en-us/dax/datediff-function-dax
Example:
Measure = DATEDIFF(SELECTEDVALUE(factCase[TIncision_DT]),SELECTEDVALUE(dimQuality[QTProcessorReview_DT]),DAY)Best Regards,
Jay
Thank you! The was the only solution that worked - and worked without creating a new column. You're incredible! Thank you!
