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 

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.

      • 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