Date Diff DAX

[gauravnarchal]

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

[mwegener · Accepted solution]

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 ()
    )