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

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
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

    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 ?