Complex Calculated Column

[Anonymous]

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?

[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

[v-xicai · Accepted 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.

[VasTg]

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

[parry2k]

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

@parry2k 

Good to know this option.

But the IF condition fails when the date is a holiday. Could you fix it?

[parry2k]

@VasTg can you explain little bit more, what you mean by fail? Error message or what?

[VasTg]

@parry2k 

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?

[parry2k]

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

[VasTg]

@parry2k 

I didn't start the thread.

The orinigal ask was to ignore the holidays. Either you misunderstood the inital ask or you didn't test your DAX. Read the first post again.

[parry2k]

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