Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
How do I create a calculated column that adds the 2 columns? I have 2 tables - Table1 and Date table.
Table1: -
Date1 | Add_Days | Final Date |
1/1/2017 | 10 | |
1/2/2017 | 15 | |
1/3/2017 | 20 | |
1/4/2017 | 25 | |
1/5/2017 | 30 | |
1/6/2017 | 35 | |
1/7/2017 | 40 | |
1/8/2017 | 45 |
Date Table:-
Date | WorkDay |
1/1/2017 | 1 |
1/2/2017 | 1 |
1/3/2017 | 1 |
1/4/2017 | 0 |
1/5/2017 | 0 |
1/6/2017 | 1 |
1/7/2017 | 1 |
1/8/2017 | 0 |
The idea is to calculate FinalDate = Date1+AddDays in the first table. However, the second table contains workday indicator. If it's 0, then it is holiday. So we only need to add days for which there are 1 in the date table.
If I directly add, it takes all the days. How can I calculate Final Date by ignoring non-working days from the second table?
Solved! Go to Solution.
Hi @Anonymous ,
You may create relationship between Table1 and Date table on date field first of all, then create columns in Date table like DAX below.
Rank1 = RANKX(FILTER(Date, Date[WorkDay]=1),Date[Date],,ASC)
Add working days = LOOKUPVALUE(Date[Date],Date[WorkDay],1,Date[Rank1],Date[Rank1]+RELATED(Table1[Add_Days]))
Finally, you can create column in Table1.
Final Date=RELATED(Calendar[Add working days])
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Hi @Anonymous ,
You may create relationship between Table1 and Date table on date field first of all, then create columns in Date table like DAX below.
Rank1 = RANKX(FILTER(Date, Date[WorkDay]=1),Date[Date],,ASC)
Add working days = LOOKUPVALUE(Date[Date],Date[WorkDay],1,Date[Rank1],Date[Rank1]+RELATED(Table1[Add_Days]))
Finally, you can create column in Table1.
Final Date=RELATED(Calendar[Add working days])
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
Please try this DAX. Hope you might have 1 to many relationship from Table2 to Table1.
Column = VAR END_DATE = 'Table 1'[Date1]+'Table 1'[Add_Days] RETURN 'Table 1'[Date1]+CALCULATE(SUM('Table 2'[WorkDay]),FILTER('Table 2','Table 2'[Date]>='Table 1'[Date1]&&'Table 2'[Date]<=END_DATE)) |
@Anonymous assuming you have relationship between these two tables on date column, here is what you do to add
Final Date =
IF ( RELATED ( DateTable[WorkDay] ) = 1, Table1[Date1] + Table1[Add_Days], Table1[Date1] )
Add your logic in else condition whatever you want, I currently put Table1[Date1] in case workday = 0
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.
@VasTg can you explain little bit more, what you mean by fail? Error message or what?
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.
When the date from table is marked as holiday(workday = 0), the IF condition is false and the else part is executed.
In my example 1,5,6 are holidays.
Table1
Date1Add_Days
1/1/2019 | 10 |
1/2/2019 | 10 |
1/3/2019 | 20 |
1/4/2019 | 25 |
1/5/2019 | 30 |
1/6/2019 | 35 |
1/7/2019 | 40 |
1/8/2019 | 45 |
Table 2
1/1/2019 | 0 |
1/2/2019 | 1 |
1/3/2019 | 1 |
1/4/2019 | 1 |
1/5/2019 | 0 |
1/6/2019 | 0 |
1/7/2019 | 1 |
1/8/2019 | 1 |
1/9/2019 | 1 |
1/10/2019 | 1 |
1/11/2019 | 1 |
1/12/2019 | 0 |
1/13/2019 | 0 |
1/14/2019 | 1 |
1/15/2019 | 1 |
1/16/2019 | 1 |
1/17/2019 | 1 |
1/18/2019 | 1 |
1/19/2019 | 0 |
1/20/2019 | 0 |
1/21/2019 | 1 |
1/22/2019 | 1 |
1/23/2019 | 1 |
1/24/2019 | 1 |
1/25/2019 | 1 |
1/26/2019 | 0 |
1/27/2019 | 0 |
1/28/2019 | 1 |
1/29/2019 | 1 |
1/30/2019 | 1 |
1/31/2019 | 1 |
2/1/2019 | 1 |
2/2/2019 | 0 |
2/3/2019 | 0 |
2/4/2019 | 1 |
2/5/2019 | 1 |
2/6/2019 | 1 |
2/7/2019 | 1 |
2/8/2019 | 1 |
2/9/2019 | 0 |
2/10/2019 | 0 |
2/11/2019 | 1 |
2/12/2019 | 1 |
2/13/2019 | 1 |
2/14/2019 | 1 |
2/15/2019 | 1 |
2/16/2019 | 0 |
2/17/2019 | 0 |
2/18/2019 | 1 |
2/19/2019 | 1 |
2/20/2019 | 1 |
2/21/2019 | 1 |
2/22/2019 | 1 |
2/23/2019 | 0 |
2/24/2019 | 0 |
2/25/2019 | 1 |
2/26/2019 | 1 |
2/27/2019 | 1 |
2/28/2019 | 1 |
Hope it helps.
By the way, which option should I use to post the DAX in the forum which appears in a box in your replies.. Table->cell?
@VasTg I asked you in original reply what you want when workday is 0 (in other words false condition). In your post you mentioned what you want if workday = 1, add days to get final date but what you want when workday is 0???
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.
@VasTg I know my DAX and I read the posts carefully, although thanks for reminding. I'm sorry I thought you started the post. Anyhow will wait @Anonymous to reply to see what he wants in case of Else.
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.
User | Count |
---|---|
87 | |
72 | |
69 | |
64 | |
56 |
User | Count |
---|---|
99 | |
91 | |
80 | |
74 | |
64 |