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...
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

     

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.

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      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

      • Baye's avatar
        Baye
        Frequent Visitor
        7 years ago

        Hi! I'm back again...

         

        I've copied your two options into two columns in my model and after I've created a measure for each one as MAX(Column2) (as the formula returns a value for each row), and SUM(Column3) (as the formula returns a value just for the last value).

         

        I've noticed that your columns return the last value in the stock, but if I try to filter the values by month, your Column 2 returns the max of total stock (not filtered by month), and your Column 3 returns "blank" if the last value in stock is in another month from the filtered one.

         

        Any of them return the correct stock for the last date/number in the selected filter.

         

        Any idea of how to solve this issue?

         

        Thanx!!!

         

        B.