Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Need help with Calculated Column

Hi   I want to create a calculated column to find the days between two dates as below   If the invoice number starts with 9 then onward (Minus) - return else return (Minus) - onward   Date - Ta...
Need Help
Show and Tell
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    gauravnarchal 

    if there is a relationship between two tables, you can try this

    Column = if(LEFT(RELATED(Table1[Invoice No]),1)="9",Table2[Onward]-Table2[Return],Table2[Return]-Table2[Onward])

    pls see the attachment below

Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
5 years ago

Hi gauravnarchal 

 

You can create a lookup column in your table 2 with below code to bring invoice number in this table:-

 

Invoice_No =
LOOKUPVALUE (
    'Date - Table 1'[Invoice No],
    'Date - Table 1'[Invoice ID], 'Date - Table 2'[Invoice ID]
)

 

Now you can create your required column with below code:-

 

Date_Diff =
VAR ifnine =
    LEFT ( 'Date - Table 2'[Invoice_No], 1 )
RETURN
    IF (
        ifnine = "9",
        DATEDIFF ( 'Date - Table 2'[Return], 'Date - Table 2'[Onward], DAY ),
        DATEDIFF ( 'Date - Table 2'[Onward], 'Date - Table 2'[Return], DAY )
    )

 

Output:-

 

Thanks,

Samarth