Need help with Calculated Column

[gauravnarchal]

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 - Table 1

Invoice ID	Invoice No
9192391	IG
9192401	DEFAUL
9192411	90989800
9192421	DEFAUL/
9192431	DEFAUL:
9192441	DEFAUL/DEL
9192451	1574 11
9192461	DEFAUL;
9192471	80987897
9192481	9090985
9192981	8088452
9193481	8088717

Date - Table 2

Invoice ID	Onward	Return
9192391	10-Aug-21	19-Aug-21
9192401	20-Aug-21	28-Aug-21
9192411	30-Aug-21	9-Sep-21
9192421	9-Sep-21	20-Sep-21
9192431	19-Sep-21	24-Sep-21
9192441	29-Sep-21	1-Oct-21
9192451	9-Oct-21	10-Oct-21
9192461	19-Oct-21	31-Oct-21
9192471	29-Oct-21	7-Nov-21
9192481	8-Nov-21	18-Nov-21
9192981	10-Sep-21	10-Sep-21
9193481	12-Sep-21	11-Sep-21

Result

Invoice ID	Onward	Return	Result
9192391	10-Aug-21	19-Aug-21	9
9192401	20-Aug-21	28-Aug-21	8
9192411	30-Aug-21	9-Sep-21	-10
9192421	9-Sep-21	20-Sep-21	11
9192431	19-Sep-21	24-Sep-21	5
9192441	29-Sep-21	1-Oct-21	2
9192451	9-Oct-21	10-Oct-21	1
9192461	19-Oct-21	31-Oct-21	12
9192471	29-Oct-21	7-Nov-21	9
9192481	8-Nov-21	18-Nov-21	-10
9192981	10-Sep-21	10-Sep-21	0
9193481	12-Sep-21	11-Sep-21	-1
		Total	37

[ryan_mayu · Accepted solution]

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

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