Forum Discussion

Baye's avatar
Baye
Frequent Visitor
7 years ago
Solved

Double condition - Stock table

Hi!
I've a problem in DAX I can't solve. Perhaps you could help me...

In an stock movement table, I have the next columns: Date-time, Product id, Movement id, Order id and Stock.
The final stock value for each product is the number that appears in the column "Stock" in the last date-time and in the last movement for each product.

A product can have multiple movements on the same date-time, so I must figure out the MAX date-time AND the MAX movement id for each product and wrap it up in a CALCULATE.

It seems as a simple DAX formula, but I cant get to it...

 

This formula works for the last NumberId or (changing NumberId for Datetime) for last Datetime:

CALCULATE(SUM[Stock];FILTER(ALL(Stock[NumberId]);Stock[NumberId]=MAX(Stock[NumberId]))

 

And now I'm trying something like this for the adding the second condition:

CALCULATE(SUM[Stock];FILTER(ALL(Stock[NumberId]);Stock[NumberId]=MAX(Stock[NumberId]))&&FILTER(ALL(Stock[Datetime]);Stock[Datetime]=MAX(Stock[Datetime])))

 

Result: #ERROR...


Thank you very much in advance!

Baye

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Baye 

    Just try this formula:

    Column 2 = 

VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) )
    )
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt )
    )
RETURN
  CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

    or

    Column 3 = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) )
    )
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( Stock, Stock[IdProduct] = EARLIER ( Stock[IdProduct] ) && Stock[Datetime] = MAXdt )
    )
RETURN
  IF (
        Stock[Datetime] = MAXdt
            && Stock[Number] = MAXNumID,
        CALCULATE ( SUM ( Stock[Stock] ) )
    )

    Result:

     

    Best Regards,
    Lin

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Baye 

    Just try this measure

    Measure = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct],'Calendar'[Month],'Calendar'[Month Number])) 
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[Number] ),
        FILTER ( ALLEXCEPT(Stock,Stock[IdProduct]), Stock[Datetime] = MAXdt )
    )

return
CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[Number] = MAXNumID && Stock[Datetime] = MAXdt )
    )

    Best Regards,

    Lin

     

11 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Baye 

    You could use this formula as below:

    Column 2 =
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[NumberId] ),
        FILTER ( Stock, Stock[Product id] = EARLIER ( Stock[Product id] ) )
    )
VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[Product id] = EARLIER ( Stock[Product id] ) )
    )
RETURN
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( Stock, Stock[NumberId] = MAXNumID && Stock[Datetime] = MAXdt )
    )

    or

    Column 3 =
VAR MAXNumID =
    CALCULATE (
        MAX ( Stock[NumberId] ),
        FILTER ( Stock, Stock[Product id] = EARLIER ( Stock[Product id] ) )
    )
VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ),
        FILTER ( Stock, Stock[Product id] = EARLIER ( Stock[Product id] ) )
    )
RETURN
    IF (
        Stock[Datetime] = MAXdt
            && Stock[NumberId] = MAXNumID,
        CALCULATE ( SUM ( Stock[Stock] ) )
    )

    https://docs.microsoft.com/en-us/dax/earlier-function-dax

    If not your case, please share your sample pbix file and expected output.

     

    Best Regards,

    Lin

    • Baye's avatar
      Baye
      Frequent Visitor
      7 years ago

      Hi!

       

      Thank you very much for your reply!

       

      I have tested the solutions you give but unfortunately they don't work, they give me the perfect stock but on the last date (max date), but if I want to filter by month having the last stock of each product in each month, they don't recalculate by that date filtered.

       

      The final objective of this formula is calculating the stock fluctuation by month.

       

      Any ideas?

       

      Thank you in advance!

       

      B.