Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |