topic Re: Using Except to filter out same orders but also compare only orders with from last workday. in DAX Commands and Tips

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

Applicable88 — Wed, 02 Feb 2022 16:08:35 GMT

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.

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

tackytechtom — Wed, 02 Feb 2022 21:05:11 GMT

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

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

Applicable88 — Thu, 03 Feb 2022 15:20:27 GMT

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 ?

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

Applicable88 — Fri, 04 Feb 2022 12:43:03 GMT

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.