DAX Formula to calculate the Order Date in Inventory Management

[Tan_LC]

Hi,

In below table, there are 2 Material No. (A060 & H846F) with the date range from 16/11/2022 till 31/01/2023.

In order to get the "Order Date (Desired column)", below criterias shall be fulfilled:

1. The Date with the earliest Stock Day(s) < 60 days shall be captured.

2. Any BLANK() cell in Stock Day(s) column or last day of the month shall be ignored/excluded.

Please use DAX Formula instead of Query Editor.

Date	Material No	Stock Day(s)	Order Date (Desired column)
16/11/2022	A060	46.5
17/11/2022	A060	46.5
18/11/2022	A060
19/11/2022	A060
20/11/2022	A060	39.8
21/11/2022	A060	53.2
22/11/2022	A060	48.8
23/11/2022	A060	49.4
24/11/2022	A060	45.3
25/11/2022	A060	69.7
26/11/2022	A060	79.4
27/11/2022	A060	53.2
28/11/2022	A060	81.5
29/11/2022	A060	82.8
30/11/2022	A060	127.8
01/12/2022	A060	56.8	01/12/2022
02/12/2022	A060	55.8	01/12/2022
03/12/2022	A060	54.8	01/12/2022
04/12/2022	A060	53.8	01/12/2022
05/12/2022	A060	52.8	01/12/2022
06/12/2022	A060	51.8	01/12/2022
07/12/2022	A060	50.8	01/12/2022
08/12/2022	A060	49.8	01/12/2022
09/12/2022	A060	48.8	01/12/2022
10/12/2022	A060	47.8	01/12/2022
11/12/2022	A060	46.8	01/12/2022
12/12/2022	A060	45.8	01/12/2022
13/12/2022	A060	44.8	01/12/2022
14/12/2022	A060	43.8	01/12/2022
15/12/2022	A060	42.8	01/12/2022
16/12/2022	A060	41.8	01/12/2022
17/12/2022	A060	40.8	01/12/2022
18/12/2022	A060	39.8	01/12/2022
19/12/2022	A060	38.8	01/12/2022
20/12/2022	A060	37.8	01/12/2022
21/12/2022	A060	36.8	01/12/2022
22/12/2022	A060	35.8	01/12/2022
23/12/2022	A060	34.8	01/12/2022
24/12/2022	A060	33.8	01/12/2022
25/12/2022	A060	32.8	01/12/2022
26/12/2022	A060	31.8	01/12/2022
27/12/2022	A060	30.8	01/12/2022
28/12/2022	A060	29.8	01/12/2022
29/12/2022	A060	28.8	01/12/2022
30/12/2022	A060	28.8	01/12/2022
31/12/2022	A060
01/01/2023	A060	29.7	01/12/2022
02/01/2023	A060	28.7	01/12/2022
03/01/2023	A060	27.7	01/12/2022
04/01/2023	A060	26.7	01/12/2022
05/01/2023	A060	25.7	01/12/2022
06/01/2023	A060	24.7	01/12/2022
07/01/2023	A060	23.7	01/12/2022
08/01/2023	A060	22.7	01/12/2022
09/01/2023	A060	21.7	01/12/2022
10/01/2023	A060	20.7	01/12/2022
11/01/2023	A060	19.7	01/12/2022
12/01/2023	A060	18.7	01/12/2022
13/01/2023	A060	17.7	01/12/2022
14/01/2023	A060	16.7	01/12/2022
15/01/2023	A060	15.7	01/12/2022
16/01/2023	A060	14.7	01/12/2022
17/01/2023	A060	13.7	01/12/2022
18/01/2023	A060	12.7	01/12/2022
19/01/2023	A060	11.7	01/12/2022
20/01/2023	A060	10.7	01/12/2022
21/01/2023	A060	9.7	01/12/2022
22/01/2023	A060	8.7	01/12/2022
23/01/2023	A060	7.7	01/12/2022
24/01/2023	A060	6.7	01/12/2022
25/01/2023	A060	5.7	01/12/2022
26/01/2023	A060	4.7	01/12/2022
27/01/2023	A060	3.7	01/12/2022
28/01/2023	A060	2.7	01/12/2022
29/01/2023	A060	1.7	01/12/2022
30/01/2023	A060	0.7	01/12/2022
31/01/2023	A060	-0.3
01/12/2022	H846F	1004.7
02/12/2022	H846F	1003.7
03/12/2022	H846F	1002.7
04/12/2022	H846F	1001.7
05/12/2022	H846F	1000.7
06/12/2022	H846F	999.7
07/12/2022	H846F	998.7
08/12/2022	H846F	997.7
09/12/2022	H846F	996.7
10/12/2022	H846F	995.7
11/12/2022	H846F	994.7
12/12/2022	H846F	993.7
13/12/2022	H846F	992.7
14/12/2022	H846F	991.7
15/12/2022	H846F	990.7
16/12/2022	H846F	989.7
17/12/2022	H846F	988.7
18/12/2022	H846F	987.7
19/12/2022	H846F	986.7
20/12/2022	H846F	985.7
21/12/2022	H846F	984.7
22/12/2022	H846F	983.7
23/12/2022	H846F	982.7
24/12/2022	H846F	981.7
25/12/2022	H846F	980.7
26/12/2022	H846F	979.7
27/12/2022	H846F	978.7
28/12/2022	H846F	977.7
29/12/2022	H846F	976.7
30/12/2022	H846F	976.7
31/12/2022	H846F
01/01/2023	H846F	44.4	01/01/2023
02/01/2023	H846F	43.4	01/01/2023
03/01/2023	H846F	42.4	01/01/2023
04/01/2023	H846F	41.4	01/01/2023
05/01/2023	H846F	40.4	01/01/2023
06/01/2023	H846F	39.4	01/01/2023
07/01/2023	H846F	38.4	01/01/2023
08/01/2023	H846F	37.4	01/01/2023
09/01/2023	H846F	36.4	01/01/2023
10/01/2023	H846F	35.4	01/01/2023
11/01/2023	H846F	34.4	01/01/2023
12/01/2023	H846F	33.4	01/01/2023
13/01/2023	H846F	32.4	01/01/2023
14/01/2023	H846F	31.4	01/01/2023
15/01/2023	H846F	30.4	01/01/2023
16/01/2023	H846F	29.4	01/01/2023
17/01/2023	H846F	28.4	01/01/2023
18/01/2023	H846F	27.4	01/01/2023
19/01/2023	H846F	26.4	01/01/2023
20/01/2023	H846F	25.4	01/01/2023
21/01/2023	H846F	24.4	01/01/2023
22/01/2023	H846F	23.4	01/01/2023
23/01/2023	H846F	22.4	01/01/2023
24/01/2023	H846F	21.4	01/01/2023
25/01/2023	H846F	20.4	01/01/2023
26/01/2023	H846F	19.4	01/01/2023
27/01/2023	H846F	18.4	01/01/2023
28/01/2023	H846F	17.4	01/01/2023
29/01/2023	H846F	16.4	01/01/2023
30/01/2023	H846F	15.4	01/01/2023
31/01/2023	H846F	14.3

Thank you.

Regards,

Lee Cheng

[v-yinliw-msft]

Hi @Tan_LC , 

You can try this method:

New columns:

Month = MONTH('Table'[Date])

Max = CALCULATE ( MAX ( 'Table'[Stock Day(s)] ), FILTER ( 'Table', 'Table'[Month] = EARLIER ( 'Table'[Month] ) && 'Table'[Material No] = EARLIER ( 'Table'[Material No] ) ) )

OrderDate(Result) =
IF (
'Table'[Max] > 60
|| 'Table'[Date] = EOMONTH ( 'Table'[Date], 0 ),
BLANK (),
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[Material No] = EARLIER ( 'Table'[Material No] )
&& 'Table'[Max] <= 60
)
)
) 

The result is:

 

 

 

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tan_LC]

Hi all, thanks for the reply.

I've tried on the DAX formula given, however when longer duration data is load, the Order Date doesn't seem to be accurate. Thus, I would like to add on additional criteria as below:

Example: 

Material A013, Order Date should always captured the latest < 60 days Date which is 01/11/2022 instead of  21/09/2022, it was due to on 01/10/2022 onwards, there were few Stock Day(s) above 60 days. 

Hope my explanation is clear and thanks for the help.

Date	Material No	Stock Day(s) (without Delivery)	To Order Date (Desired column)
21/09/2022	A013	34
22/09/2022	A013	32.8
23/09/2022	A013	38.3
24/09/2022	A013	37.3
25/09/2022	A013	36.6
26/09/2022	A013	34.9
27/09/2022	A013	34.9
28/09/2022	A013	41.9
29/09/2022	A013	41.6
30/09/2022	A013	50.1
01/10/2022	A013	154.1
02/10/2022	A013	142.4
03/10/2022	A013	138.9
04/10/2022	A013	150.3
05/10/2022	A013	150.6
06/10/2022	A013	150.9
07/10/2022	A013	148.5
08/10/2022	A013	147.4
09/10/2022	A013	154
10/10/2022	A013	133.8
11/10/2022	A013	132.7
12/10/2022	A013	124.9
13/10/2022	A013	125.6
14/10/2022	A013	121.7
15/10/2022	A013	118.3
16/10/2022	A013	119.5
17/10/2022	A013	131.3
18/10/2022	A013	131.5
19/10/2022	A013	133.6
20/10/2022	A013	156.1
21/10/2022	A013	164.4
22/10/2022	A013	184.4
23/10/2022	A013
24/10/2022	A013
25/10/2022	A013
26/10/2022	A013	189.1
27/10/2022	A013	168.8
28/10/2022	A013	157.8
29/10/2022	A013	163
30/10/2022	A013	182.7
31/10/2022	A013	233.8
01/11/2022	A013	44.1	01/11/2022
02/11/2022	A013	38.6	01/11/2022
03/11/2022	A013	41.3	01/11/2022
04/11/2022	A013
05/11/2022	A013
06/11/2022	A013
07/11/2022	A013	39.9	01/11/2022
08/11/2022	A013	34.5	01/11/2022
09/11/2022	A013	34.6	01/11/2022
10/11/2022	A013	33.6	01/11/2022
11/11/2022	A013	33.2	01/11/2022
12/11/2022	A013	32.5	01/11/2022
13/11/2022	A013	31.3	01/11/2022
14/11/2022	A013	29.3	01/11/2022
15/11/2022	A013	30.8	01/11/2022
16/11/2022	A013	30.1	01/11/2022
17/11/2022	A013	28.2	01/11/2022
18/11/2022	A013
19/11/2022	A013
20/11/2022	A013	23.4	01/11/2022
21/11/2022	A013	28.5	01/11/2022
22/11/2022	A013	44.7	01/11/2022
23/11/2022	A013	45	01/11/2022
24/11/2022	A013	46.1	01/11/2022
25/11/2022	A013	45	01/11/2022
26/11/2022	A013	51.2	01/11/2022
27/11/2022	A013	33.5	01/11/2022
28/11/2022	A013	49.3	01/11/2022
29/11/2022	A013	49.8	01/11/2022
30/11/2022	A013	75.1
01/12/2022	A013	32.9	01/11/2022
02/12/2022	A013	31.9	01/11/2022
03/12/2022	A013	30.9	01/11/2022
04/12/2022	A013	29.9	01/11/2022
05/12/2022	A013	28.9	01/11/2022
06/12/2022	A013	27.9	01/11/2022
07/12/2022	A013	26.9	01/11/2022
08/12/2022	A013	25.9	01/11/2022
09/12/2022	A013	24.9	01/11/2022
10/12/2022	A013	23.9	01/11/2022
11/12/2022	A013	22.9	01/11/2022
12/12/2022	A013	21.9	01/11/2022
13/12/2022	A013	20.9	01/11/2022
14/12/2022	A013	19.9	01/11/2022
15/12/2022	A013	18.9	01/11/2022
16/12/2022	A013	17.9	01/11/2022
17/12/2022	A013	16.9	01/11/2022
18/12/2022	A013	15.9	01/11/2022
19/12/2022	A013	14.9	01/11/2022
20/12/2022	A013	13.9	01/11/2022
21/12/2022	A013	12.9	01/11/2022
22/12/2022	A013	11.9	01/11/2022
23/12/2022	A013	10.9	01/11/2022
24/12/2022	A013	9.9	01/11/2022
25/12/2022	A013	8.9	01/11/2022
26/12/2022	A013	7.9	01/11/2022
27/12/2022	A013	6.9	01/11/2022
28/12/2022	A013	5.9	01/11/2022
29/12/2022	A013	4.9	01/11/2022
30/12/2022	A013	4.9	01/11/2022
31/12/2022	A013
01/01/2023	A013	3.3	01/11/2022
02/01/2023	A013	2.3	01/11/2022
03/01/2023	A013	1.3	01/11/2022
04/01/2023	A013	0.3	01/11/2022
05/01/2023	A013	-0.7	01/11/2022
21/09/2022	A060	38.1
22/09/2022	A060	37
23/09/2022	A060	60.7
24/09/2022	A060	60
25/09/2022	A060	59.6
26/09/2022	A060	56
27/09/2022	A060	56.2
28/09/2022	A060	55
29/09/2022	A060	55
30/09/2022	A060	67.5
01/10/2022	A060	195.1
02/10/2022	A060	179
03/10/2022	A060	174.6
04/10/2022	A060	151.9
05/10/2022	A060	152.5
06/10/2022	A060	155.9
07/10/2022	A060	152.7
08/10/2022	A060	151.3
09/10/2022	A060	159.3
10/10/2022	A060	155.7
11/10/2022	A060	155.4
12/10/2022	A060	148.6
13/10/2022	A060	151.1
14/10/2022	A060	133.1
15/10/2022	A060	129.7
16/10/2022	A060	131.8
17/10/2022	A060	131.1
18/10/2022	A060	131.5
19/10/2022	A060	132.8
20/10/2022	A060	131.5
21/10/2022	A060	124.1
22/10/2022	A060	139.7
23/10/2022	A060
24/10/2022	A060
25/10/2022	A060
26/10/2022	A060	142.9
27/10/2022	A060	128.7
28/10/2022	A060	120.7
29/10/2022	A060	123.5
30/10/2022	A060	137.8
31/10/2022	A060	364.9
01/11/2022	A060	70.7
02/11/2022	A060	61.6
03/11/2022	A060	67.4
04/11/2022	A060
05/11/2022	A060
06/11/2022	A060
07/11/2022	A060	67.9
08/11/2022	A060	55.8
09/11/2022	A060	55.5
10/11/2022	A060	56.6
11/11/2022	A060	55.6
12/11/2022	A060	54.9
13/11/2022	A060	53.3
14/11/2022	A060	45.5
15/11/2022	A060	47.5
16/11/2022	A060	46.5
17/11/2022	A060	46.5
18/11/2022	A060
19/11/2022	A060
20/11/2022	A060	39.8
21/11/2022	A060	53.2
22/11/2022	A060	48.8
23/11/2022	A060	49.4
24/11/2022	A060	45.3
25/11/2022	A060	69.7
26/11/2022	A060	79.4
27/11/2022	A060	53.2
28/11/2022	A060	85
29/11/2022	A060	86.5
30/11/2022	A060	133.6
01/12/2022	A060	59.4	01/12/2022
02/12/2022	A060	58.4	01/12/2022
03/12/2022	A060	57.4	01/12/2022
04/12/2022	A060	56.4	01/12/2022
05/12/2022	A060	55.4	01/12/2022
06/12/2022	A060	54.4	01/12/2022
07/12/2022	A060	53.4	01/12/2022
08/12/2022	A060	52.4	01/12/2022
09/12/2022	A060	51.4	01/12/2022
10/12/2022	A060	50.4	01/12/2022
11/12/2022	A060	49.4	01/12/2022
12/12/2022	A060	48.4	01/12/2022
13/12/2022	A060	47.4	01/12/2022
14/12/2022	A060	46.4	01/12/2022
15/12/2022	A060	45.4	01/12/2022
16/12/2022	A060	44.4	01/12/2022
17/12/2022	A060	43.4	01/12/2022
18/12/2022	A060	42.4	01/12/2022
19/12/2022	A060	41.4	01/12/2022
20/12/2022	A060	40.4	01/12/2022
21/12/2022	A060	39.4	01/12/2022
22/12/2022	A060	38.4	01/12/2022
23/12/2022	A060	37.4	01/12/2022
24/12/2022	A060	36.4	01/12/2022
25/12/2022	A060	35.4	01/12/2022
26/12/2022	A060	34.4	01/12/2022
27/12/2022	A060	33.4	01/12/2022
28/12/2022	A060	32.4	01/12/2022
29/12/2022	A060	31.4	01/12/2022
30/12/2022	A060	31.4	01/12/2022
31/12/2022	A060
01/01/2023	A060	32.5	01/12/2022
02/01/2023	A060	31.5	01/12/2022
03/01/2023	A060	30.5	01/12/2022
04/01/2023	A060	29.5	01/12/2022
05/01/2023	A060	28.5	01/12/2022

Regards,

Lee Cheng

[v-yinliw-msft · Accepted solution]

Hi @Tan_LC ,

You can try this method:

New two columns:

Max =
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        'Table',
        'Table'[Material No] = EARLIER ( 'Table'[Material No] )
            && 'Table'[Stock Day(s) (without Delivery)] >= 60
            && 'Table'[Date] <> EOMONTH ( 'Table'[Date], 0 )
    )
)

Result =
IF (
    'Table'[Stock Day(s) (without Delivery)] >= 60
        || 'Table'[Stock Day(s) (without Delivery)] = BLANK ()
        || 'Table'[Date] = EOMONTH ( 'Table'[Date], 0 ),
    BLANK (),
    IF ( 'Table'[Date] < 'Table'[Max], BLANK (), 'Table'[Max] + 2 )
)

 The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @Tan_LC 

try to add a new column with the code below:

OrderDate =

VAR CurrentMaterial = TableName[MaterialNo]

VAR table1 =

FILTER(

    TableName,

    TableName[StockDays]<=60

        && TableName[MaterialNo] = CurrentMaterial

)

VAR value1 =

MAXX(table1, TableName[StockDays])

VAR table2 =

FILTER(

    table1,

    TableName[StockDays] = value1

)

VAR table3 =

SELECTCOLUMNS(table2, "Date", TableName[Date])

VAR value2 =

IF(

    COUNTROWS(table3)=1,

    table3

)

RETURN

IF(    

TableName[StockDays]<>BLANK()

    && TableName[Date] >= value2

    && VAR value3 = ENDOFMONTH(TableName[Date])

            RETURN TableName[Date]<>value3,

value2

)

i tried and it worked like this: