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,

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

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

  • KR300's avatar
    KR300
    Helper III
    3 years ago

    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?