Using Except to filter out same orders but also compare only orders with from last workday.

[Applicable88]

Hello,

I want to check the performance of a dispatch team. Everyday we have a record of how much shipping we prepared and shipped. Those orders have the Status "S" for shipped.

We have another record of data, which are the leftover orders from the last working day. Those orders were also prepared, but were too late for shipping, so in the meantime we gave them the status "P" for prepared. So every "P" ordernumber will become an "S" order in the end. 

At the end of a working day we calculate the performance, of the team. We count how many orders were shipped today, but we have to deduct those orders which are also on the "P" table, since the main preparation work were already done on a workday before. The last workday can be three days ago when there was a weekend in between or one day for all other workdays. When the factory were closed because of christmas vacation the last working day can also be over one week or more ago. So the last working day cannot be a fixed value like  date = Max(Table(Date) - 1 or so. It needs to be the max (date) of the preparation table, but smaller than the shipping date. 

I have  sample data only for describing one particular workday which is a monday and the last workday was a Friday:

The shipping table: 

OrderNo.		Date		Status		Sales
1111		10/01/2022		S		1000
1112		10/01/2022		S		1000
1113		10/01/2022		S		1000
1119		10/01/2022		S		1500
1118		10/01/2022		S		1500
1000		10/01/2022		S		3000

The preparation table:

OrderNo.		Date		Status		Sales
1111		09/01/2022		P		1000
1112		09/01/2022		P		1000
1113		09/01/2022		P		1000
1114		09/01/2022		P		8000
1115		09/01/2022		P		10000
1116		09/01/2022		P		1500

The right return of counts for the shipping date 10/01//2022 should be 3 orders, since the orders with the number 1119,1118 and 1000, are not listed in the preparation table from the last workday listed 

I know how to count only those which are not in preparation table:

CountOrdersMeasure = 

Var _Orders_S =

                     Calculatetable (

                                       Values ( TableT [OrderNo.] ) , 

                                        TableT [Status] = "S"

                    )

Var _Orders_P =

                     Calculatetable (

                                       Values ( TableY [OrderNo.] ) , 

                                        TableT [Status] = "P"

                    )

Return

             Calculate ( 

                       Count ( TableT[OrderNo.] ),

                       Except (

                                   _Orders_S,

                                   _Orders_P

                        )

            )

But this function doesn't take into account that for every shipping day I only want to except those orders which are also in the preparation table which belongs to the last workday. 

I hope someone can help me out with that. 

Thank you very much in advance. 

Best. 

[Applicable88]

@tackytechtom thank you so much. I found the little mistake, and now I get the correct results. Since the preparation table needs to be filtered to "last working day" the folliwing part belongs to the variable _Orders_P and not _Orders_S. 

PreparationTable[Date] = _LatestPreparationDate

CountOrdersMeasure = 
Var _CurrentDate = 
Max ( ShippingTable[Date] )

Var _LatestPreparationDate = 
Calculate ( 
    Max ( PreparationTable[Date] ),
    PreparationTable[Date] < _CurrentDate 
)

Var _Orders_S =
Calculatetable (
    Values ( ShippingTable[OrderNo] ) , 
    ShippingTable[Status] = "S"
    
)

Var _Orders_P =
Calculatetable (
    Values ( PreparationTable[OrderNo] ) , 
    PreparationTable[Status] = "P",
    PreparationTable[Date] = _LatestPreparationDate
)

Return
Calculate ( 
    Count ( ShippingTable[OrderNo] ),
    Except (
        _Orders_S,
        _Orders_P
    )
)

Thanks so much!

Best. 

[tackytechtom · Accepted solution]

Hi @Applicable88,

Does this one work for you?

CountOrdersMeasure = 
Var _CurrentDate = 
Max ( ShippingTable[Date] )

Var _LatestPreparationDate = 
Calculate ( 
    Max ( PreparationTable[Date] ),
    PreparationTable[Date] < _CurrentDate 
)

Var _Orders_S =
Calculatetable (
    Values ( ShippingTable[OrderNo] ) , 
    ShippingTable[Status] = "S",
    PreparationTable[Date] = _LatestPreparationDate
)

Var _Orders_P =
Calculatetable (
    Values ( PreparationTable[OrderNo] ) , 
    PreparationTable[Status] = "P"
)

Return
Calculate ( 
    Count ( ShippingTable[OrderNo] ),
    Except (
        _Orders_S,
        _Orders_P
    )
)

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Applicable88]

Hello @tackytechtom , thank you so much so far! With that I think I got closer to my wanted outcome. 

But there is still one thing I need to change and I still haven't find the mistake yet. I guess its still the variables currentdates and lastpreparationdate which aren't working correctly.

I only get the right count when I use two additional slicer, one for shipping date and one for preparation date. 

Only when I limit the two dates like 09/01/2022 and 10/01/2022 it will give me the right result. So far I know that the except function works correctly. 

Might there be a mistake in the variables ?