Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Date diff with between two tables

I' have Two Table with date like this :

Table 1

Date1          | ID_KEY | TITLE|

15/01/2020 |1           | DOC1

16/02/2020 | 1          | DOC2

15/03/2020 | 1          |DOC3

15/03/2020 |2           |DOC1

14/02/2021 |2           |DOC3

 

Table 2

DATE2 | ID_KEY|

10/01/2000 | 1

10/03/2004 |2

 

I search to make the différence between DATE2 and DATE1 by DAY  When ID_KEY are the same and TITLE = DOC1

i try datediff but the return message is : It is impossile to determine unik value for DATE . I try with Crossfilter with ID_KEY with one but i have the same message.

How can i do this datediff.

 

1 Reply

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    there are a lot solutions

    for example you could use LOOKUPVALUE() column in table2:

    Column = LOOKUPVALUE(Table1[Date1], Table1[ID_KEY], Table2[ID_KEY], Table1[TITLE], "DOC1")

    but if you have more then 1 row in table1 with the same ID_KEY and title="Doc1" use a measure

    Column = CALCULATE(FIRSTNONBLANK(Table1[Date1], 1), FILTER(ALL(Table1), Table1[ID_KEY] = SELECTEDVALUE(Table2[ID_KEY]) && Table1[TITLE]= "DOC1") )