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

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?