Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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   ...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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's avatar
VasTg
Icon for Memorable Member rankMemorable Member
6 years ago

parry2k 

 

Good to know this option.

 

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

 

 

 

 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

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

    • VasTg's avatar
      VasTg
      Icon for Memorable Member rankMemorable Member
      6 years ago

      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/201910
      1/2/201910
      1/3/201920
      1/4/201925
      1/5/201930
      1/6/201935
      1/7/201940
      1/8/201945

       

       

      Table 2

      1/1/20190
      1/2/20191
      1/3/20191
      1/4/20191
      1/5/20190
      1/6/20190
      1/7/20191
      1/8/20191
      1/9/20191
      1/10/20191
      1/11/20191
      1/12/20190
      1/13/20190
      1/14/20191
      1/15/20191
      1/16/20191
      1/17/20191
      1/18/20191
      1/19/20190
      1/20/20190
      1/21/20191
      1/22/20191
      1/23/20191
      1/24/20191
      1/25/20191
      1/26/20190
      1/27/20190
      1/28/20191
      1/29/20191
      1/30/20191
      1/31/20191
      2/1/20191
      2/2/20190
      2/3/20190
      2/4/20191
      2/5/20191
      2/6/20191
      2/7/20191
      2/8/20191
      2/9/20190
      2/10/20190
      2/11/20191
      2/12/20191
      2/13/20191
      2/14/20191
      2/15/20191
      2/16/20190
      2/17/20190
      2/18/20191
      2/19/20191
      2/20/20191
      2/21/20191
      2/22/20191
      2/23/20190
      2/24/20190
      2/25/20191
      2/26/20191
      2/27/20191
      2/28/20191

       

       

      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's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

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