Forum Discussion
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 - 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 |
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
2 Replies
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
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