Forum Discussion

game1's avatar
game1
Icon for Helper III rankHelper III
2 years ago
Solved

Subtract the 3 dates from different tables

I have Table2[dateBefore], Table1[dateBegin] and Table1[dateEnd].

So, I need to subtract the 3 dates, so I can have the result in DAYS:  so something like Table2[dateBefore] - Table1[dateEnd] - Table1[dateBegin] 

So, I create a new coloumn call DatesDiff = DATEDIFF(Table2[dateBefore], Table1[dateBegin], DAY) + DATEDIFF(Table2[dateBefore], Table1[dateEnd], DAY)

But the new coloumn call DatesDiff is create in Table1, so I can have acces to Table2[dateBefore].

How can I do if I want to subtract column from differents tables?  I need to have an ID common to the 2 tables to do that?

Can I do that using join?

Please, give complete answer.

Date
DAX
DIFF
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi game1 ,

     

    According to your description, you can use the SELECTEDVALUE () function to realize that you want to subtract columns from different tables,
    Here is an example:
    My Sample:
    Table1:

    Table2:

    The formula is as follows.

    DatesDiff =
DATEDIFF ( SELECTEDVALUE ( Table2[dateBefore] ), Table1[dateBegin], DAY )
    + DATEDIFF ( SELECTEDVALUE ( Table2[dateBefore] ), Table1[dateEnd], DAY )

    Result is as below.

     

    Best Regards,
    Yulia Yan

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies