Forum Discussion
Calculated Column or New measure
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.
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)
2 Replies
- amitchandak
Super User
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)Thank you so much!
