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.

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

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.