Double condition - Stock table

[Baye]

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 value for each product is the number that appears in the column "Stock" in the last date-time and in the last movement for each product.

A product can have multiple movements on the same date-time, so I must figure out the MAX date-time AND the MAX movement id for each product and wrap it up in a CALCULATE.

It seems as a simple DAX formula, but I cant get to it...

This formula works for the last NumberId or (changing NumberId for Datetime) for last Datetime:

CALCULATE(SUM[Stock];FILTER(ALL(Stock[NumberId]);Stock[NumberId]=MAX(Stock[NumberId]))

And now I'm trying something like this for the adding the second condition:

CALCULATE(SUM[Stock];FILTER(ALL(Stock[NumberId]);Stock[NumberId]=MAX(Stock[NumberId]))&&FILTER(ALL(Stock[Datetime]);Stock[Datetime]=MAX(Stock[Datetime])))

Result: #ERROR...

Thank you very much in advance!

Baye

[v-lili6-msft · Accepted solution]

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]

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]

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]

hi, @Baye 

Sample data and expected output would help tremendously.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Lin

[Baye]

Hi,

Here's the excel workbook, it's a sample model regarding the two different cases I found:

https://orikamimail-my.sharepoint.com/:x:/g/personal/jbustillo_biadvisors_es/EQ0kKN_WMeFPhnQWVHxFQKA...

Thank you very much!

B.

[v-lili6-msft · Accepted solution]

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]

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]

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]

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]

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]

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 · Accepted solution]

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