Combine Dates and Times from different Tables

Hi
I have a model which I have attached to this question.
From the data model, I am to calculate in minutes:

The difference between Sale date (DateID) and Completion Date (CompletionDateID)

The difference between Sale date (DateID) and ReviewDateID (ReviewDateID)

The difference between Completion Date (CompletionDate ) and Review Date (ViewDateID)

I will have to add the date field to the Time from Time field for all relevant date field before arriving at the correct answer.
The issue is the model has this information from Date table and Time table which are joined by broken relationships and I am unable to use the USERELATIONSHIP without CALCULATE function.
I truly need your help with is.
Thank you

SalesData_Correct.pbix (2.2 MB)

so, reading your post, my first question is why do you want to avoid using CALCULATE? Have you encountered problems with it in your full model?

It just did not work for me. I did try to implement that

Any help, please? I will greatly appreciate it. Thank you

sorry for the delay, I had a lot of work to do at my regular job :slight_smile:

Step 1 - I was having problems with your table having random calculated columns, so I recreated a portion of it. (see Sales2 table attached)
Step 2 - I added an index to the Sales2 table, as Iā€™m assuming your real data has some type of product or invoice index information
Step 3 - I used TREATAS instead of USERELATIONSHIP to build the formulas:

Sale vs Completion Date variance =
VAR CompletionDate =
CALCULATE( FIRSTDATE( Dates[Date] ),
TREATAS( VALUES( Sales2[CompletionDateID] ), Dates[DateID] ))
VAR SalesDate =
CALCULATE( FIRSTDATE( Dates[Date] ),
TREATAS( VALUES( Sales2[DateID] ), Dates[DateID] ))
RETURN
DATEDIFF( SalesDate, CompletionDate, DAY )

NOTE: TREATAS does not require a connection to your date table for any of the relationships, but I did include an inactive relationship to CompletionDateID, and an active relationship to DateID
The reason I did this was to demonstrate how TREATAS works the same with all three measures, even though there is no relationship for the ReviewDateID, and the two relationships described above for the other two ID fields.

See attached for solution
eDNA solution - comparing two dates.pbix (2.2 MB)

1 Like

Hello @Heather,
Thank you so much. You are a present help in trouble. Sorry I had to use the random function to stimulate the concept of the model I am working with.

I really do appreciate your time on this. One issue though, you did not add the Time from the Time table to the date. e.g, [Date] + [Time] to get date and minute as you would have in DateTime data type. The solution requires that date and time be added for each date field before finding the variance in minutes.
I truly am grateful for the help and look forward to your reply
Thank you for your help. So grateful

@upwardD

Sorry that I missed that requirement.
please see the attached, I have added only a measure to add the time value to the Sale and Completion dates - from this, you should be able to expand and create your other values.

Note that the new value computes to an HOUR value, instead of a DAY value as I did with the previous measures

image

eDNA solution - comparing two datetimes.pbix (2.2 MB)

2 Likes

Absolutely brilliant
Thank you

1 Like