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,

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
Not applicable
8 years ago

Anonymous,

 

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!!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    Great! Thanks!