Forum Discussion

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

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

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 reco...
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago

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.