Date Comparison between two Related Tables

[dkernen]

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_SEjBsJS...

Any help on a formula/measure would be so helpful.  Thank you!

@dax, @DateComparisons

[v-jayw-msft · Accepted solution]

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

[dkernen]

Thank you!  The was the only solution that worked - and worked without creating a new column.  You're incredible!  Thank you!

[vanessafvg]

the one has datetime values the other only had the date no time in it is that correct?

[dkernen]

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!