DAX Help

[Tejinder]

Thanks for clicking on this

This is my sample data: 

BarcodeID	Order ID	BOX sequence	TotalBoxes	Received(Y/N)	received
100620559	1959	1	4	Y	2023-07-18 8:16
100620565	1959	4	4	Y	2023-07-18 8:16
100620566	1959	2	4	Y	2023-07-18 8:16
100620567	1959	3	4	N	null
100673629	1961506	4	4	Y	2023-07-21 9:01
100673630	1961506	2	4	Y	2023-07-21 9:01
100673631	1961506	3	4	Y	2023-07-21 9:01
100673632	1961506	1	4	Y	2023-07-21 9:01
100686199	196	6	7	Y	2023-07-21 2:16
100686200	196	7	7	Y	2023-07-21 2:16
100686201	196	5	7	Y	2023-07-21 2:16
100686203	196	3	7	Y	2023-07-21 2:52
100686204	196	4	7	Y	2023-07-21 2:13
100686205	196	1	7	Y	2023-07-21 2:13
100686206	196	2	7	Y	2023-07-21 2:13
100682964	19616002	5	5	N	null
100682965	19616002	4	5	N	null
100682966	19616002	3	5	N	null
100682967	19616002	2	5	N	null
100682968	19616002	1	5	N	null

This is from an inventory scan; scans are done daily on barcode levels.
Every order has multiple boxes and unique barcodes for those boxes, sometimes some or all are not scanned (missing). I would like to summarize it into partially missing, missing, or complete,
For example:

1959 is partial
1961506 is complete
19616002 is missing

There are time stamps for every scan and sometimes some of the orders are scanned multiple times and new records are created, it doesn’t replace existing scans or lack of.

The way I tried to do it is by creating a table by summarizing DAX measure, but it doesn't work for me operationally because some orders are scanned multiple times, Let's take order 1959 for example. If it has 3/4 boxes scanned it will show as partial but if another box is scanned at a later date, it will count it as 4 scans and show it as complete.

I would like to have it in a measure so I can filter it by time/date

Here is my DAX:

Output =

VAR _1 =

SUMMARIZE(

'Table','Table'[Order ID],

"TotBox",Max( 'Table'[TotalBoxes] ),

"Count",

COUNTROWS(

FILTER(

'Table', 'Table'[Received(Y/N)] = "Y"

) )

)

VAR _2 =

ADDCOLUMNS(

_1,"Status",

IF(

[Count]= BLANK(),"Missing",

IF(

[Count] = [TotBox],"Completed",

"Partially Completed"

) ) )

VAR _Result =

SELECTCOLUMNS(

_2,"OrderID",[Order ID],

"Status",[Status]

)

RETURN

_Result

Here is the result 

Any help is appreciated.

[amitchandak · Accepted solution]

@Tejinder , Create a new column

Status =
var _count =
CALCULATE(
COUNTROWS('Table'),
'Table'[Order ID] = earlier('Table'[Order ID]),
'Table'[Received(Y/N)] = "Y"
)
return
IF(
_count = 0, "Missing",
IF(
_count = MAX('Table'[TotalBoxes]), "Completed",
"Partially Completed"
)
)

New measure

StatusMeasure =
VAR _date = MAX('Table'[received])
RETURN
CALCULATE(
VALUES('Table'[Status]),
FILTER('Table', 'Table'[received] <= _date)
)

[Tejinder]

Thanks for your help
It does not work, here are the screenshots, 

The idea is that,  i can use a date/time range from a filter. to select a particular window

[Tejinder]

..