Forum Discussion
Subtracting Dates within Two Different Tables
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,
LydiaAnonymous,
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,
LydiaAnonymous,
Right-click on your table , then select "New Measure" and apply your DAX.
Regards,
Lydia
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
Anonymous,
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.
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,
LydiaI'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.
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