cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
essakkiappan
Helper I
Helper I

adding custom column by comparing data in another table

Hi All,

 

I have two tables(A, B). 

Table A

Task idTitleCreated Date
1Title 113-05-2020
2Title 210-02-2020
3Title 323-03-2020
4Title 413-12-2019
5Title 510-04-2020

 

Table B:

Start DateEnd dateIteration Number
01-12-201931-12-2019      1
01-01-202031-01-2020      2
01-02-202029-02-2020      3
01-03-202031-03-2020      4
01-04-202030-04-2020      5
01-05-202031-05-2020      6

 

And the result table should be:

Task idTitleCreated DateIteration Number
1Title 113-05-2020       6
2Title 210-02-2020       3
3Title 323-03-2020       4
4Title 413-12-2019       1
5Title 510-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.

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

8 REPLIES 8
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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
Super User III
Super User III

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

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

@essakkiappan 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.

 

image.png

 

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!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

 

image.png

@essakkiappan 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors