Calculated Column or New measure

[YonaD]

Hello Everyone,

I am new to power BI and DAX and I was trying to create a calculated column in which the values depend on the calculation from another table.

My data look like these:

Table 1

Project ID	Project Name	Activity	Start Date	Finish Date
1	Project A	Task 1	2020-01-15
1	Project A	Task 2		2020-01-31
2	Project B	Task 1	2021-03-19
2	Project B	Task 2		2021-12-11
3	Project C	Task 1	2022-01-09
3	Project C	Task 2		2022-01-31

Table 2

Project ID	Project Name	Activity	Duration
1	Project A	Task 2
2	Project B	Task 2
3	Project C	Task 2

I was trying to create that Duration column which is the datediff of task 1 start date and task 2 finish date. Could anyone please help me solve this? I tried calculated column but I am getting one same answer for all. Thank you in advanced.

[amitchandak · Accepted solution]

@YonaD , a new column

new column in table 2 =
datediff(minx(filter(Table1, Table1[Project ID] = Table2[Project ID] && Table1[Activity] ="Task 1"),Table1[Start Date]),
maxx(filter(Table1, Table1[Project ID] = Table2[Project ID] && Table1[Activity] ="Task 2"),Table1[Finish Date]),day)

[YonaD]

Thank you so much!