Forum Discussion
Complex Calculated Column
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?
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.
10 Replies
- parry2k
Super User
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
- VasTg
Memorable Member
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))If it helps, mark it as a solution.Kudos are nice too. 
v-xicaiCommunity Support
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.
- v-xicai
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
