Forum Discussion
adding custom column by comparing data in another table
Hi All,
I have two tables(A, B).
Table A
| Task id | Title | Created Date |
| 1 | Title 1 | 13-05-2020 |
| 2 | Title 2 | 10-02-2020 |
| 3 | Title 3 | 23-03-2020 |
| 4 | Title 4 | 13-12-2019 |
| 5 | Title 5 | 10-04-2020 |
Table B:
| Start Date | End date | Iteration Number |
| 01-12-2019 | 31-12-2019 | 1 |
| 01-01-2020 | 31-01-2020 | 2 |
| 01-02-2020 | 29-02-2020 | 3 |
| 01-03-2020 | 31-03-2020 | 4 |
| 01-04-2020 | 30-04-2020 | 5 |
| 01-05-2020 | 31-05-2020 | 6 |
And the result table should be:
| Task id | Title | Created Date | Iteration Number |
| 1 | Title 1 | 13-05-2020 | 6 |
| 2 | Title 2 | 10-02-2020 | 3 |
| 3 | Title 3 | 23-03-2020 | 4 |
| 4 | Title 4 | 13-12-2019 | 1 |
| 5 | Title 5 | 10-04-2020 | 5 |
I want to add a column into table A which should be Iteration Number by comparing Created Date in between Start Date and End date.
I tried by merging two tables, appending it, adding a conditional column, custom column but nothing worked as expected.
I need a suggestion on how to handle this.
Thanks in advance.
8 Replies
Anonymous , A new column like this in first table
New column in Table A = minx(filter(TableB, TableB[StartDate]<= TableA[Created date] && TableB[End Date]>= TableA[Created date]),TableB[Iteration Number])
Thanks, @amitchandak for the response. I'm new to PowerBI. When I try to add the formula you gave in the Custom Column window, I'm not getting the Table B column[StartDate/EndDate].
Anonymous I think you have a similar post, anyhow add the following column, change column and table name as per your model.
Iteration = VAR __createdDate = Tasks[Created Date] RETURN CALCULATE ( MAX ( Iteration[Iteration Number] ), __createdDate >= Iteration[Start Date], __createdDate <= Iteration[End date] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Thanks parry2k for response, I faced some issue with my old post while adding tables so created the new post.
I'm new to PowerBI. So, can you please explain a little bit where I need to add this formula? and why we are using MAX function here? I need to pull only the Iteration Number based on the creation date.
Anonymous go to your task table, and in the menu, select add a new column, max is used to get maximum iteration number if it found two records, try this column and then let me know for any issue.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
