Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Subtracting Dates within Two Different Tables

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 ha...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
    Anonymous
    8 years ago

    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's avatar
    Anonymous
    8 years ago

    Anonymous,

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

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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

 

 

Table FieldSample Data
SCCalls.EquipmentID12345
SCCalls.CallNumber201123
SCCalls.CallTypeIDP
SCCalls.CustomerID

123

SCCalls.CreateDate1/1/18 2:00pm
SCCalls.CloseDate1/5/18 5:00pm
  
  
Table FieldSample Data
SCEquipments.EquipmentID12345
SCEquipments.CustomerID

123

SCEquipments.CreateDate1/1/18 2:00pm
SCEquipments.InstallDate1/2/16 3:00pm

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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