adding custom column by comparing data in another table

[Anonymous]

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.

[amitchandak]

@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])

[Anonymous]

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].

[parry2k]

@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!

[Anonymous]

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.

[parry2k]

@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!

[Anonymous]

@parry2k I tried that but getting the below error.

DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[parry2k]

@Anonymous can you make sure created date, start date, and end date is of data type, as you can clearly see in the error, one or more columns are not of the same data type.

I would recommend you take a guided learning course on Power BI to understand the basics.

[Anonymous]

Sorry, @parry2k. I have changed the data type of Created Date, Start Date, and End Date to Date but I didn't refresh the table. After refreshing the table I didn't see any error in the formula. anyhow the data has not populated in the column.

Can you provide me the link where i can refer the formulas we can use?