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

     

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

hi, Baye 

if I try to filter the values by month

What field do you use to filter the value by month, isn't month field? and could you share a simple sample pbix file and expected output? that would help tremendously.

Do mask sensitive data before uploading.

Best Regards,
Lin

Baye's avatar
Baye
Frequent Visitor
7 years ago

Here's the link to the document, I've mounted it with two months for each product (and only two products just to test the formula). I've copied your three formulas in the model.

 

In the stock page you would see the expected values for each product and month, in the pivot page of the book you'll see the result of the formulas.

 

https://1drv.ms/x/s!Al6VLYxbJKorfeJTjarUNOoMOS8

 

Thanx!!

 

B.

 

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