Forum Discussion
Solved
If statement (Dates)
I need help to create a calculated column in Table 1as below
If number (Table 1) = number (Table 2)
&
Date (Table 1) Equal too and greator than the Start Date (Table 2) and all dates that fall within the next 1yr of the start date return Yes, else No. Also If no start date is found then return Error
Below is the table
Table 1
| Number | InvoiceNumber | Date |
| 13000288 | 6167 | 03-01-22 0:00 |
| 13000299 | 6172 | 06-01-22 0:00 |
| 10100300 | 6173 | 09-01-22 0:00 |
| 1501000 | BPL830098 | 01-01-22 0:00 |
| 13000288 | BPL847494 | 03-01-22 0:00 |
| 13000302 | 847977 | 01-01-22 0:00 |
| 13000224 | 847981 | 01-01-22 0:00 |
| 100400 | 848087 | 01-01-22 0:00 |
| 13000104 | 848125 | 01-01-22 0:00 |
| 2002100 | 848239 | 01-01-22 0:00 |
| 2002100 | 848242 | 01-01-22 0:00 |
| 1301502 | 848379 | 02-01-22 0:00 |
| 1802700 | 848408 | 02-01-22 0:00 |
| 107100 | 848428 | 01-01-22 0:00 |
| 700500 | 848482 | 01-01-22 0:00 |
| 104500 | 848490 | 01-01-22 0:00 |
| 104500 | 848491 | 01-01-22 0:00 |
| 104500 | 848492 | 01-01-22 0:00 |
| 304100 | 848493 | 01-01-22 0:00 |
| 104500 | 848494 | 01-01-22 0:00 |
| 104500 | 848495 | 01-01-22 0:00 |
| 104500 | 848497 | 01-01-22 0:00 |
| 13000316 | 848498 | 01-01-22 0:00 |
| 13000316 | 848499 | 01-01-22 0:00 |
| 104500 | 848500 | 01-01-22 0:00 |
| 104500 | 848501 | 01-01-22 0:00 |
| 13000316 | 848502 | 01-01-22 0:00 |
| 13000316 | 848503 | 01-01-22 0:00 |
| 104500 | 848504 | 01-01-22 0:00 |
| 104500 | 848505 | 01-01-22 0:00 |
| 104500 | 848506 | 01-01-22 0:00 |
| 304100 | 848507 | 01-01-22 0:00 |
| 13000288 | 848508 | 01-01-22 0:00 |
| 1905300 | 848509 | 01-01-22 0:00 |
| 302612 | 848512 | 01-01-22 0:00 |
| 104500 | 848513 | 01-01-22 0:00 |
| 1303200 | 848514 | 01-01-22 0:00 |
| 104500 | 848517 | 02-01-22 0:00 |
| 1301500 | 848518 | 01-01-22 0:00 |
| 1300900 | 848519 | 01-01-22 0:00 |
| 1300900 | 848520 | 01-01-22 0:00 |
| 302612 | 848521 | 01-01-22 0:00 |
| 1301500 | 848522 | 01-01-22 0:00 |
| 802200 | 848523 | 02-01-22 0:00 |
| 1301500 | 848528 | 02-01-22 0:00 |
| 1903700 | 848531 | 02-01-22 0:00 |
| 1802701 | 848532 | 02-01-22 0:00 |
| 1301800 | 848533 | 01-01-22 0:00 |
| 13000313 | 848534 | 02-01-22 0:00 |
| 302612 | 848535 | 02-01-22 0:00 |
| 302612 | 848538 | 01-01-22 0:00 |
| 1303200 | 848539 | 01-01-22 0:00 |
| 1801700 | 848541 | 02-01-22 0:00 |
| 104500 | 848542 | 02-01-22 0:00 |
| 1501006 | 848544 | 02-01-22 0:00 |
| 302612 | 848545 | 01-01-22 0:00 |
| 13000106 | 848546 | 02-01-22 0:00 |
| 13000288 | 848547 | 01-01-22 0:00 |
| 1501006 | 848548 | 02-01-22 0:00 |
| 1501006 | 848549 | 02-01-22 0:00 |
| 104500 | 848550 | 02-01-22 0:00 |
| 1501006 | 848551 | 02-01-22 0:00 |
| 104500 | 848552 | 02-01-22 0:00 |
| 13000232 | 848553 | 02-01-22 0:00 |
| 701201 | 848554 | 02-01-22 0:00 |
| 104500 | 848555 | 02-01-22 0:00 |
| 104500 | 848556 | 02-01-22 0:00 |
| 1501006 | 848557 | 02-01-22 0:00 |
| 404100 | 848558 | 02-01-22 0:00 |
| 13000158 | 848559 | 02-01-22 0:00 |
| 1300201 | 848560 | 02-01-22 0:00 |
| 1501006 | 848561 | 02-01-22 0:00 |
| 1101500 | 848562 | 02-01-22 0:00 |
| 104500 | 848567 | 02-01-22 0:00 |
| 104500 | 848568 | 02-01-22 0:00 |
| 1501001 | 848569 | 02-01-22 0:00 |
| 1301500 | 848570 | 02-01-22 0:00 |
| 1501006 | 848571 | 02-01-22 0:00 |
| 1501006 | 848572 | 02-01-22 0:00 |
| 1501006 | 848573 | 02-01-22 0:00 |
| 1501001 | 848574 | 02-01-22 0:00 |
| 1301500 | 848576 | 02-01-22 0:00 |
| 104500 | 848577 | 02-01-22 0:00 |
| 104500 | 848578 | 02-01-22 0:00 |
| 1101500 | 848579 | 02-01-22 0:00 |
| 13000106 | 848580 | 02-01-22 0:00 |
| 104500 | 848581 | 02-01-22 0:00 |
| 1301502 | 848582 | 02-01-22 0:00 |
| 13000288 | 848583 | 02-01-22 0:00 |
| 1301500 | 848584 | 02-01-22 0:00 |
Table 2
| Number | Start Date |
| 100400 | 01-03-20 0:00 |
| 104500 | 01-09-17 0:00 |
| 107100 | 01-11-19 0:00 |
| 302612 | 01-04-15 0:00 |
| 304100 | 01-01-20 0:00 |
| 404100 | |
| 700500 | 01-12-12 0:00 |
| 701201 | 01-08-13 0:00 |
| 802200 | 01-06-18 0:00 |
| 1101500 | 01-11-16 0:00 |
| 1300201 | 01-01-21 0:00 |
| 1300900 | 01-04-19 0:00 |
| 1301500 | 01-12-14 0:00 |
| 1301502 | 01-12-14 0:00 |
| 1301800 | 01-07-14 0:00 |
| 1303200 | 01-03-19 0:00 |
| 1501000 | 01-01-18 0:00 |
| 1501001 | 01-01-18 0:00 |
| 1501006 | 01-01-18 0:00 |
| 1801700 | 01-11-15 0:00 |
| 1802700 | 01-07-20 0:00 |
| 1802701 | 01-07-20 0:00 |
| 1903700 | 01-02-15 0:00 |
| 1905300 | 01-05-19 0:00 |
| 2002100 | 01-11-19 0:00 |
| 10100300 | 01-01-00 0:00 |
| 13000104 | 01-01-21 0:00 |
| 13000106 | 01-01-21 0:00 |
| 13000158 | 01-01-21 0:00 |
| 13000224 | 01-01-21 0:00 |
| 13000232 | 01-01-21 0:00 |
| 13000288 | 01-05-21 0:00 |
| 13000299 | 01-03-17 0:00 |
| 13000302 | |
| 13000313 | 01-01-07 0:00 |
| 13000316 | 01-01-07 0:00 |
Column = SWITCH(TRUE(),ISBLANK(RELATED('Table 2'[Start Date])),"Error",'Table 1'[Date]<RELATED('Table 2'[Start Date]),"No",'Table 1'[Date]>EDATE(RELATED('Table 2'[Start Date]),12),"No","Yes")Note: Tables are joined via [Number]. See attached.
1 Reply
Column = SWITCH(TRUE(),ISBLANK(RELATED('Table 2'[Start Date])),"Error",'Table 1'[Date]<RELATED('Table 2'[Start Date]),"No",'Table 1'[Date]>EDATE(RELATED('Table 2'[Start Date]),12),"No","Yes")Note: Tables are joined via [Number]. See attached.