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

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