Forum Discussion
Solved
Date Diff DAX
Hello
I need to create a calculation formula to get
If the days between the Ship date and the return date is between 0-2 days return “0-02 Days”
If the days between the Ship date and the return date is between 03-07 days return “03-07 Days”
If the days between the Ship date and the return date is between 08-14 days return “08-14 Days”
If the days between the Ship date and the return date is between 15-21 days return “15-21 Days”
If the days between the Ship date and the return date is between 22-30 days return “22-30 Days”
If the days between the Ship date and the return date is greater than 30 days return “above 31Days”
Data
| Ship Date | Return Date |
| 28-Jan-00 | 26-Feb-21 |
| 9-May-08 | |
| 6-Oct-19 | 6-Oct-19 |
| 13-Oct-19 | 13-Oct-19 |
| 11-Dec-19 | 12-Dec-19 |
| 20-Dec-19 | 10-Jan-20 |
| 3-Jan-20 | 24-Jan-20 |
| 25-Jan-20 | 24-Mar-20 |
| 28-Jan-20 | 28-Jan-21 |
| 30-Jan-20 | 2-Feb-20 |
| 15-Feb-20 | 16-Feb-20 |
| 18-Feb-20 | 19-Feb-20 |
| 28-Feb-20 | 3-Mar-20 |
| 9-Mar-20 | 11-Mar-20 |
| 17-Mar-20 | 23-Mar-20 |
| 25-Mar-20 | 5-Apr-20 |
| 25-Mar-20 | 6-Apr-20 |
| 26-Mar-20 | 4-Apr-20 |
| 26-Mar-20 | 7-Apr-20 |
| 26-Mar-20 | 8-Apr-20 |
| 26-Mar-20 | 11-Apr-20 |
| 27-Mar-20 | 9-Apr-20 |
| 27-Mar-20 | 25-Apr-20 |
| 29-Mar-20 | 2-Apr-20 |
| 29-Mar-20 | 5-Apr-20 |
| 1-Apr-20 | 2-Apr-20 |
| 1-Apr-20 | 13-Apr-20 |
| 1-Apr-20 | 19-Apr-20 |
| 1-Apr-20 | 2-Apr-21 |
| 2-Apr-20 | 25-Apr-20 |
| 3-Apr-20 | 11-Apr-20 |
| 3-Apr-20 | 13-Apr-20 |
| 4-Apr-20 | 9-Apr-20 |
| 4-Apr-20 | 13-Apr-20 |
| 4-Apr-20 | 19-Apr-20 |
| 5-Apr-20 | 5-Apr-20 |
| 7-Apr-20 | 10-Apr-20 |
| 7-Apr-20 | 24-Apr-20 |
| 16-Apr-20 | 25-Apr-20 |
| 19-Apr-20 | 25-Apr-20 |
| 23-Apr-20 | 24-Apr-20 |
| 23-Apr-20 | 3-May-20 |
| 25-Apr-20 | 6-May-20 |
| 25-Apr-20 | 17-Jul-20 |
| 30-Apr-20 | 3-May-20 |
| 1-May-20 | 4-May-20 |
| 5-May-20 | 5-May-20 |
| 5-May-20 | 21-May-20 |
| 6-May-20 | 16-May-20 |
| 13-May-20 | 14-May-20 |
| 14-May-20 | 19-May-20 |
| 15-May-20 | 21-Jun-20 |
| 17-May-20 | 9-Jun-20 |
| 19-May-20 | 31-May-20 |
| 20-May-20 | 29-May-20 |
| 21-May-20 | 21-May-20 |
| 22-May-20 | 30-May-20 |
| 22-May-20 | 7-Jun-20 |
| 24-May-20 | 28-May-20 |
| 30-May-20 | 5-Jun-20 |
| 14-Jun-20 | 20-Jun-20 |
| 18-Jun-20 | 28-Jun-20 |
| 23-Jun-20 | 23-Jun-20 |
| 23-Jun-20 | 8-Jul-20 |
| 25-Jun-20 | 4-Jul-20 |
| 25-Jun-20 | 19-Aug-20 |
| 26-Jun-20 | 29-Aug-20 |
| 2-Jul-20 | 5-Jul-20 |
| 4-Jul-20 | 28-Aug-20 |
| 5-Jul-20 | 14-Aug-20 |
| 8-Jul-20 | 22-Jul-20 |
| 8-Jul-20 | 6-Aug-20 |
| 10-Jul-20 | 2-Aug-20 |
| 10-Jul-20 | 8-Aug-20 |
| 11-Jul-20 | 11-Jul-20 |
| 12-Jul-20 | 5-Aug-20 |
| 13-Jul-20 | 7-Aug-20 |
| 15-Jul-20 | 26-Jul-20 |
| 15-Jul-20 | 9-Aug-20 |
| 17-Jul-20 | 4-Aug-20 |
| 24-Jul-20 | 24-Jul-20 |
| 27-Jul-20 | 28-Jul-20 |
| 27-Jul-20 | 26-Aug-20 |
| 30-Jul-20 | 8-Aug-20 |
| 31-Jul-20 | 21-Aug-20 |
| 31-Jul-20 | 28-Aug-20 |
| 2-Aug-20 | 24-Aug-20 |
| 6-Aug-20 | 17-Aug-20 |
| 16-Aug-20 | 16-Aug-20 |
| 2-Sep-20 | 3-Sep-20 |
| 5-Sep-20 | 6-Sep-20 |
| 6-Sep-20 | 6-Sep-20 |
| 13-Sep-20 | 5-Jan-21 |
| 14-Sep-20 | 14-Sep-20 |
| 1-Oct-20 | 10-Oct-20 |
| 6-Oct-20 | 13-Oct-20 |
| 8-Oct-20 | 9-Jan-21 |
| 10-Oct-20 | 9-Nov-20 |
| 10-Oct-20 | 15-Nov-20 |
| 10-Oct-20 | 2-Dec-20 |
| 17-Oct-20 | 23-Oct-20 |
| 23-Oct-20 | 23-Oct-20 |
| 24-Oct-20 | 10-Nov-20 |
| 1-Nov-20 | 11-Nov-20 |
| 7-Nov-20 | 22-Nov-20 |
| 13-Nov-20 | 13-Nov-20 |
| 17-Nov-20 | 17-Dec-20 |
| 20-Nov-20 | 20-Dec-20 |
| 25-Nov-20 | 1-Dec-20 |
| 1-Dec-20 | 31-Dec-20 |
| 2-Dec-20 | 14-Dec-20 |
| 2-Dec-20 | 19-Dec-20 |
| 2-Dec-20 | 14-Dec-21 |
| 6-Dec-20 | 11-Dec-20 |
| 8-Dec-20 | 6-Jan-21 |
| 10-Dec-20 | 11-Dec-20 |
| 10-Dec-20 | 17-Dec-20 |
| 10-Dec-20 | 6-Jan-21 |
| 10-Dec-20 | 8-Jan-21 |
| 11-Dec-20 | 13-Dec-20 |
| 11-Dec-20 | 18-Dec-20 |
| 12-Dec-20 | 3-Jan-21 |
| 13-Dec-20 | 16-Dec-20 |
| 13-Dec-20 | 24-Dec-20 |
| 13-Dec-20 | 30-Dec-20 |
| 14-Dec-20 | 19-Dec-20 |
| 14-Dec-20 | 12-Jan-21 |
| 16-Dec-20 | 16-Dec-20 |
| 16-Dec-20 | 14-Jan-21 |
| 17-Dec-20 | 17-Dec-20 |
| 17-Dec-20 | 16-Jan-21 |
| 18-Dec-20 | 20-Dec-20 |
| 18-Dec-20 | 14-Jan-21 |
| 19-Dec-20 | 24-Dec-20 |
| 20-Dec-20 | 22-Dec-20 |
| 20-Dec-20 | 30-Dec-20 |
| 20-Dec-20 | 31-Dec-20 |
| 20-Dec-20 | 29-Jan-21 |
| 21-Dec-20 | 27-Dec-20 |
| 22-Dec-20 | 22-Dec-20 |
| 23-Dec-20 | 23-Dec-20 |
| 23-Dec-20 | 29-Dec-20 |
| 23-Dec-20 | 3-Jan-21 |
| 24-Dec-20 | 24-Dec-20 |
| 26-Dec-20 | 14-Jan-21 |
| 27-Dec-20 | 27-Dec-20 |
| 28-Dec-20 | 5-Jan-21 |
| 29-Dec-20 | 3-Jan-21 |
| 30-Dec-20 | 30-Dec-20 |
| 30-Dec-20 | 31-Dec-20 |
| 30-Dec-20 | 1-Jan-21 |
| 30-Dec-20 | 29-Jan-21 |
| 31-Dec-20 | 3-Jan-21 |
| 31-Dec-20 | 4-Jan-21 |
| 31-Dec-20 | 23-Mar-21 |
| 31-Dec-20 | 26-Mar-21 |
| 1-Jan-21 | 1-Jan-21 |
| 1-Jan-21 | 30-Jan-21 |
| 1-Jan-21 | 1-Jun-21 |
| 2-Jan-21 | 2-Jan-21 |
| 2-Jan-21 | 3-Jan-21 |
| 2-Jan-21 | 4-Jan-21 |
| 2-Jan-21 | 7-Jan-21 |
| 3-Jan-21 | 3-Jan-21 |
| 3-Jan-21 | 4-Jan-21 |
| 3-Jan-21 | 7-Jan-21 |
| 3-Jan-21 | 9-Jan-21 |
| 3-Jan-21 | 10-Jan-21 |
Hi gauravnarchal ,
create a calculated column with this DAX formula.Diff = VAR diff = DATEDIFF ( 'Table'[Ship Date], 'Table'[Return Date], DAY ) RETURN SWITCH ( TRUE (), diff >= 0 && diff <= 2, "0-02 Days", diff >= 3 && diff <= 7, "03-07 Days", diff >= 8 && diff <= 14, "08-14 Days", diff >= 15 && diff <= 21, "15-21 Days", diff >= 22 && diff <= 30, "22-30 Days", diff > 30, "above 31 Days", BLANK () )
1 Reply
Hi gauravnarchal ,
create a calculated column with this DAX formula.Diff = VAR diff = DATEDIFF ( 'Table'[Ship Date], 'Table'[Return Date], DAY ) RETURN SWITCH ( TRUE (), diff >= 0 && diff <= 2, "0-02 Days", diff >= 3 && diff <= 7, "03-07 Days", diff >= 8 && diff <= 14, "08-14 Days", diff >= 15 && diff <= 21, "15-21 Days", diff >= 22 && diff <= 30, "22-30 Days", diff > 30, "above 31 Days", BLANK () )