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 

First, you should know that calculated column and calculate table can't be affected by any slicer. 
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Second, you could use “all” functions to add a measure

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

 

Measure = VAR MAXdt =
    CALCULATE (
        MAX ( Stock[Datetime] ), ALLEXCEPT(Stock,Stock[IdProduct])) 
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 )
    )

By the way: add month column into red part above.

 

Best Regards,

Lin

Baye's avatar
Baye
Frequent Visitor
7 years ago

Ok, I've created a measure instead a calculated column as you propose.

 

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

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

The result measure is valid in PowerPivot, but now the problem is that when I insert the measure into the pivot table, it returns blank for each product.

 

I'm really lost, is the first time I can't solve a problem...

 

Really appreciate your help,

 

B.

  • 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