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