Subtracting Dates within Two Different Tables

[Anonymous]

Hello,

I have a report I'm working on using DirectQuery. I'd like to subtract the difference in two dates that reside in two tables but based off a variable in one of the tables.

So, we have a table for Equipment and a table for SCalls. I need to find the difference in the Equipment Create Date and the SCall Date for when the SCall Type is a certain value.

Can I do this in DirectQuery?

Thanks,

Michelle

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create similar measures as below. If the DAX don't help, please share dummy data of your tables.

Measure = CALCULATE(max(SCCalls[Create Date]),FILTER(SCCalls,SCCalls[Scall Type]="test2"))

Difference = DATEDIFF(MAX(SCEquipments[Create Date]),SCEquipments[Measure],DAY)

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@Anonymous,

What if the type of CallTypeID? If it is numerical type, change your DAX to the following:

Measure= CALCULATE(MAX(SCCalls[CreateDate]), FILTER(SCCalls, SCCalls[CallTypeID]=55))

Regards,
Lydia

[v-yuezhe-msft]

@Anonymous,

Is there any relationship between the Equipment table and SCalls table?

You should be able to use DAX or Power BI query code to achieve the above requirement, please share dummy data of the two tables and post expected result so that we can provide you appropriate DAX formula or Power Query code.

Regards,
Lydia

[KR300]

I have 2 tables 
AA tables has                                                 BB table has
ArrivalDateTime                                             EventDateTime
10/15/2022 07:46:36 PM                               10/15/2022 07:46:36 PM 
10/15/2022  11:49:23 AM                               10/15/2022  11:52:23 PM

10/15/2022 10:43:04 PM                               10/15/2022 10:54:04 PM

10/15/2022 11:38:45 PM                               10/16/2022 00:01:45 PM

10/15/2022 10:49:45 PM                               10/15/2022 11:01:45 PM
10/15/2022 11:51:45 PM                               10/16/2022 00:10:45 PM

can we do time difference calculations like this ( if we have Common column which has Many to Many Relation) ( whatif we have Many to One Reation ).

I have one more tables which has EndDateTime
can we lookup these columns into main tables?

[Anonymous]

@v-yuezhe-msft,

Yes, they are related by EquipmentID field. The formula I'm using is:

VALUE(DATEDIFF(SCEquipments[CreateDate],SCCalls[CreateDate],DAY))

The error it's producing is: A single value for column "CreateDate" in table "SCCalls" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The issue is that each equipment can have multiple service calls. I'd like to subtract the dates from every service call and then filter the calls down to what I'm looking for but it seems I have to filter within the formula instead.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create similar measures as below. If the DAX don't help, please share dummy data of your tables.

Measure = CALCULATE(max(SCCalls[Create Date]),FILTER(SCCalls,SCCalls[Scall Type]="test2"))

Difference = DATEDIFF(MAX(SCEquipments[Create Date]),SCEquipments[Measure],DAY)

Regards,
Lydia

[Anonymous]

I'm still getting the same error. The two tables are quite large but here is some of the important information:

Table Field	Sample Data
SCCalls.EquipmentID	12345
SCCalls.CallNumber	201123
SCCalls.CallTypeID	P
SCCalls.CustomerID	123
SCCalls.CreateDate	1/1/18 2:00pm
SCCalls.CloseDate	1/5/18 5:00pm
Table Field	Sample Data
SCEquipments.EquipmentID	12345
SCEquipments.CustomerID	123
SCEquipments.CreateDate	1/1/18 2:00pm
SCEquipments.InstallDate	1/2/16 3:00pm

Let me know if I need to give you more information. 

[v-yuezhe-msft]

@Anonymous，

Right click your table and choose new measure, then apply my DAX. If you still get issues, please post expected result based on the sample data you share and post a screenshot about your current scenario.

Regards,
Lydia

[Anonymous]

I was wrong about the CallTypeID above. Apparently it's one of the few ID's that have numerical values instead of letters.

I put the measure in and got the below error.

I tried adding the Value function right before the (SCCAlls[CallTypeID]="55")) but I got this:

Thanks,

[v-yuezhe-msft · Accepted solution]

@Anonymous,

What if the type of CallTypeID? If it is numerical type, change your DAX to the following:

Measure= CALCULATE(MAX(SCCalls[CreateDate]), FILTER(SCCalls, SCCalls[CallTypeID]=55))

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft,

Okay, this got the Measure to work correctly but now I have an error with the column. Can you take a look? You have been so helpful!!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Right-click on your table , then select "New Measure" and apply your DAX.

Regards,
Lydia

[Anonymous]

Great! Thanks!